YTD

[atasgao]

Hi!

So, On the 8th business day of a month onwards I would like to show the data till last month and before the 8th business day I would like to show the data for the month before the last month. 

The formula I am using here 

CurrentDate = TODAY()
MonthBack_1 = Date( YEAR(DimDate[CurrentDate]-25),MONTH(DimDate[CurrentDate]-25),01)
MonthBack_2 = Date( YEAR(DimDate[CurrentDate]-60),MONTH(DimDate[CurrentDate]-60),01)

EightBusinessday = CALCULATE(MIN(DimDate[Day]),Filter(DimDate, DimDate[CurrentMonthBusinessDay]=8 ))

M_YTDBudget = CALCULATE([M_AnnualBudgetByMonth],FILTER(ALL( DimDate[Month] ), DimDate[Month] <= IF(Day(Now())<EightBusinessday, MAX(Dimdate[monthBack_2]), MAX(Dimdate[monthBack_1]))))

This M_YTD is giving me data for all the months.

Thanks, 

Aakanksha

[v-jayw-msft · Accepted solution]

Hi @atasgao ,

Create a column in DimDate table to get 8th business_day for each month.

YearMonth = FORMAT(dimdate[Date],"YYYYMM")

business_day = IF(WEEKDAY('dimdate'[Date],2)<=5,'dimdate'[Date])

is_eight = RANKX(FILTER(dimdate,dimdate[business_day]<>BLANK()&&dimdate[YearMonth]=EARLIER(dimdate[YearMonth])),dimdate[business_day],,ASC)

8_business_day = CALCULATE(MAX(dimdate[Date]),FILTER(dimdate,dimdate[YearMonth]=EARLIER(dimdate[YearMonth])&&[is_eight]=8))

 

Then create measures to get the value of last month or before last month.

last_1_month = CALCULATE(SUM('Table'[Budget]),FILTER(ALL('Table'),FORMAT(EDATE('Table'[Date],+1),"YYYYMM")=SELECTEDVALUE(dimdate[YearMonth])))

last_2_month = CALCULATE(SUM('Table'[Budget]),FILTER(ALL('Table'),FORMAT(EDATE('Table'[Date],+2),"YYYYMM")=SELECTEDVALUE(dimdate[YearMonth])))

eight_bussiness = IF(SELECTEDVALUE(dimdate[8_business_day])<=TODAY(),'Table'[last_1_month],'Table'[last_2_month])

last_1_month_YTD = CALCULATE(SUM('Table'[Budget]),FILTER(ALL('Table'),FORMAT(EDATE('Table'[Date],+1),"YYYYMM")<=SELECTEDVALUE(dimdate[YearMonth])))

last_2_month_YTD = CALCULATE(SUM('Table'[Budget]),FILTER(ALL('Table'),FORMAT(EDATE('Table'[Date],+2),"YYYYMM")<=SELECTEDVALUE(dimdate[YearMonth])))

eight_bussiness_YTD = IF(SELECTEDVALUE(dimdate[8_business_day])<=TODAY(),'Table'[last_1_month_YTD],'Table'[last_2_month_YTD])

You could also add [project_name] to filter codition.

Pbix as attached.

Best Regards,

Jay

[AlexisOlson]

How about this instead?

M_YTDBudget =
VAR EightBusinessday =
    CALCULATE (
        MIN ( DimDate[Date] ),
        DimDate[CurrentMonthBusinessDay] = 8
    )
VAR EndDate =
    IF (
        TODAY () < EightBusinessday,
        EOMONTH ( TODAY (), -2 ),
        EOMONTH ( TODAY (), -1 )
    )
RETURN
    CALCULATE (
        [M_AnnualBudgetByMonth],
        DimDate[Date] <= EndDate
    )

[atasgao]

@AlexisOlson When I use the above formula for all the months it is giving the same value. 

[AlexisOlson]

Right. The logic is based on TODAY(), not MonthName.

If you want something different, please explain how CurrentMonthBusinessDay is defined (is it current month only or does it give the business day in every month?) and how you expect the calculation to work in your screenshot scenario.

[atasgao]

@AlexisOlson The CurrentMonthBusinessDay gives business day each month. So, basically I want the show the data till December as the 8th business day for this month is 12th Jan. but before 8th business day ie before 12th Jan it should show the data till Nov. 

Now the above calculations gives me aggregated value for all the months.

Thanks, 

Aakanksha

[AlexisOlson]

Is the KEEPFILTERS version what you're looking for then?

M_YTDBudget =
VAR EightBusinessday =
    CALCULATE (
        MIN ( DimDate[Date] ),
        DimDate[CurrentMonthBusinessDay] = 8
    )
VAR EndDate =
    IF (
        TODAY () < EightBusinessday,
        EOMONTH ( TODAY (), -2 ),
        EOMONTH ( TODAY (), -1 )
    )
RETURN
    CALCULATE (
        [M_AnnualBudgetByMonth],
        KEEPFILTERS ( DimDate[Date] <= EndDate )
    )

[atasgao]

Attaching a screenshot - where the end date is showing 11/30/2021. and if I select the EightBusinessday as 15 then the date is 1/24/2022 So, the data should populate till the 11/30/2021 and not till december month. And after 1/24/2022 the data will show till Dec 

Now, still the data is populating for all the months if I use keepfilters version.  I want to filter out the YTD data for till the month of nov.  

Any idea how can I achieve that? 

Before I was using the below formula but as the year changed now the formula doesnt work 

 = CALCULATE([M_AnnualBudgetByMonth],FILTER(ALL( DimDate[Month] ), DimDate[Month] <= IF(Day(Now())<=15,Month(Now())-2,MONTH(Now())-1)))

Thanks