Forum Discussion

asteinbach's avatar
asteinbach
Frequent Visitor
5 years ago
Solved

Dynamic YTD Based On Slicer

Hello,   A bit newer to power BI here. I'm trying to create a measure that calculates YTD based on a slicer. I can't attach the dashboard because of privacy issues, but I will do my best to describ...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi asteinbach ,

    Modify your 2 measures as below:

     

    Revenue_Calc_2021_Actual = Calculate(sum('Revenue_COGS'[Value]),'Revenue_COGS'[Account] = "Revenue",'Revenue_COGS'[Year]=2021,'Revenue_COGS'[Scenario] = "ACTUAL",FILTER(Revenue_COGS,DATE('Revenue_COGS'[Year],Revenue_COGS[Month Number],1)<=SELECTEDVALUE('Months'[Date_Format]))


    EBITDA_Actual = (Revenue_COGS[Revenue_Calc_2021_Actual] - Calculate(sum(Costs[Value]),Costs[Year]=2021,Costs[Scenario]="ACTUAL",FILTER('Costs',
DATE('Costs'[Year],'Costs'[Month Number],1)<=SELECTEDVALUE('Months'[Date_Format]))) + [Dep_Amo_Impair_Actual])

     

    And you will see:

    As for the orientation from Department_Hiearchy to Revenue_COGS is single and one to many,so when you use month from table Months,the data cant be filtered correctly.That's why you have to add the month filter to the expressions.

    Check my .pbix file attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!