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!

     

     

     

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi asteinbach ，

 

Pls share your pbi file with dummy data.

 

Best Regards,
Kelly

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

  • asteinbach's avatar
    asteinbach
    Frequent Visitor
    5 years ago

     

     

    Let me know if this link works. Essentially, the calculation should (theoretically) be a calculate based on the selectedvalue of the slicer. So for "May", it should be the sum of all values equal to or before May in the table. However, I couldn't get that to work.

    • v-kelly-msft's avatar
      v-kelly-msft
      Icon for Community Support rankCommunity Support
      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!

       

       

       

      • asteinbach's avatar
        asteinbach
        Frequent Visitor
        5 years ago

        Hi Kelly,

         

        Thank you for sending that over. It seems this still doesn't achieve what I'm hoping to get. Those calculations still yield only monthly results (although with significantly more efficent expressions that the ones I currently have.) From what I can tell, when I change the slicers, those calculations still yield only monthly results. What I'm hoping to get is the results from the calcluations you altered, but the summation of the currently selected month in the slicer, and all prior months before that, if that makes sense. 

         

        For example, if May is selected in the slicer, the YTD revenue for Actual (total) should be 146,292,211. If April is selected, the YTD revenue should 82,288,481. The current calculation still spits out 64,003,730 for May and 57,647,527 for April. Those are correct for those specific months, they aren't YTD numbers. I know these from offline calcualtions.