计算每个会计月份的剩余天数 [英] Count Remaining Days per Fiscal Month

问题描述

基于此帖子，我正在构建一个财政日历：

Based on this Post I am building a fiscal calendar:

以下Dax会生成DAX日历：

The following Dax generates a DAX Calendar:

Calendar = 
VAR BaseCalendar =
    CALENDAR ( DATE ( 2016; 1; 1 ); DATE ( 2025; 12; 31 ) )
RETURN
    GENERATE (
        BaseCalendar;
        VAR BaseDate = [Date]
        VAR Year =
            YEAR ( BaseDate )
        VAR MonthNumber =
            MONTH ( BaseDate )
        VAR WeekNumber =
            WEEKNUM ( BaseDate )
        VAR FWeek =
            WEEKNUM ( BaseDate; 21 )
        RETURN
            ROW (
                "Day"; BaseDate;
                "Year"; Year;
                "Month Number"; MonthNumber;
                "Month"; FORMAT ( BaseDate; "mmmm" );
                "Year Month"; FORMAT ( BaseDate; "yyyy-mm" );
                "Day of Week"; FORMAT ( BaseDate; "dddd" );
                "Day of Week Short"; FORMAT ( BaseDate; "ddd" );
                "Week"; WeekNumber;
                "Year-Week"; Year & "-" & WeekNumber;
                "Fiscal Week"; FWeek;
                "Fiscal Month Short"; SWITCH (
                    TRUE ();
                    FWeek IN { 1; 2; 3; 4 }; "Jan";
                    FWeek IN { 5; 6; 7; 8 }; "Feb";
                    FWeek IN { 9; 10; 11; 12; 13 }; "Mar";
                    FWeek IN { 14; 15; 16; 17 }; "Apr";
                    FWeek IN { 18; 19; 20; 21 }; "May";
                    FWeek IN { 22; 23; 24; 25; 26 }; "Jun";
                    FWeek IN { 27; 28; 29; 30 }; "Jul";
                    FWeek IN { 31; 32; 33; 34 }; "Aug";
                    FWeek IN { 35; 36; 37; 38; 39 }; "Sep";
                    FWeek IN { 40; 41; 42; 43 }; "Oct";
                    FWeek IN { 44; 45; 46; 47 }; "Nov";
                    FWeek IN { 48; 49; 50; 51; 52; 53 }; "Dec"
            )
    )

现在我需要一个列，该列显示我每个日期每个会计月份的剩余天数。
我建议我需要第一列，该列累积每月的工作日。
然后，我需要第二列从对应月份的总和中减去此值。

Now I need a column that shows me remaining days for the respective fiscal month for every date. I would suggest I need a first column, that accumulates the working days per month. Then I need a second column that subtract this value from the total sum for the corresponding month.

如何在同一日历中将其添加到日历中

How would you add build that into the calendar in the same approach provided above?

推荐答案

取决于您定义的工作日。我想它是星期一至星期五
您需要在上面的DAX中添加新列以返回1或0

Depending on what you define as a working day. I'm presuming it is Mon-Fri You need a new column added to your above DAX to return a 1 or 0

IsWeekDay = SWITCH(TRUE(), WEEKDAY(Calendar[Date], 2) <= 5, 1, 0)

因此，周一至周五它将返回1，其他则返回0。
接下来为您的月份开始日期添加一列，在此示例中，我使用的是日历月：

So it will return 1 for Mon-Fri, 0 for anything else. Next add a column for your month start date, I'm using the Calendar Month in this example:

MonthStart = STARTOFMONTH(Calendar[Date])

对于日期04/04/2020，它将返回日期为01/04/2020

For a date of 04/04/2020, it will return a date of 01/04/2020

下一个新列应使用EARILER总结工作日

The next new column should sum up the working days using EARILER

Working Days = 
CALCULATE ( SUM('Calendar'[IsWeekDay]),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] >= EARLIER ( 'Calendar'[Date] )
            &&  EARLIER('Calendar'[MonthStart]) = STARTOFMONTH('Calendar'[Date])
    )
)

您必须针对您的财政月开始日期进行调整

You'll have to adjust it for your Fiscal Month Start Date

这篇关于计算每个会计月份的剩余天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！