Forum Discussion

atasgao's avatar
atasgao
Helper II
4 years ago
Solved

YTD

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

7 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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's avatar
      atasgao
      Helper II
      4 years ago

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

       

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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