Forum Discussion

yaman123's avatar
yaman123
Icon for Post Partisan rankPost Partisan
5 years ago
Solved

YTD without a date column

Hi all, 

 

I need help with creating a measure which will give me the YTD value without a date column. 

 

I have a measure which calculates the budget per category

Total Budget Values = SUMX(VALUES(FM_COST_CENTRE_REPORT_PBI[CATEGORY]),CALCULATE(MAX(FM_COST_CENTRE_REPORT_PBI[BUDGET_VALUE])))
 
I have a accounting period column which is used as a slicer to select the period to report on. 
 
I need to be able to select a period and the budget should calculate the YTD value for that category. 
 
E.g if i select period 4, the category IT should have a total value of 2000 as this sums periods 1,2,3 and 4. 
 
Any help is appreciated! 
 
TIA
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    yaman123 

    You can create a separate distinct table to use the period as the slicer, then create a measure to get expected value.

     

    Period = DISTINCT('Table'[Period])
     
    Measure = CALCULATE(SUM('Table'[Budget Value]),FILTER(ALLSELECTED('Table'),[Category]=MAX([Category]) && [Period]<=SELECTEDVALUE(Period[Period])))

     

     

    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

4 Replies

  • FarhanAhmed's avatar
    FarhanAhmed
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Can you please share some sample data for your requirement along with desired results?

    • yaman123's avatar
      yaman123
      Icon for Post Partisan rankPost Partisan
      5 years ago

      Hi FarhanAhmed 

       

      I am hoping this makes sense. 

       

      In yellow is what i expect. It should give me the YTD total per category when i select a account period from a slicer. E.g if i select account period 4, the total for consultancy is 600 and for IT costs is 120000. 

       

      • FarhanAhmed's avatar
        FarhanAhmed
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Try something like this.

         

        _YTD = CALCULATE(SUM('YTD'[Budget]),FILTER(ALLEXCEPT('YTD','YTD'[Category]),'YTD'[PeriodID]<=MAX('YTD'[PeriodID])))
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    yaman123 

    You can create a separate distinct table to use the period as the slicer, then create a measure to get expected value.

     

    Period = DISTINCT('Table'[Period])
     
    Measure = CALCULATE(SUM('Table'[Budget Value]),FILTER(ALLSELECTED('Table'),[Category]=MAX([Category]) && [Period]<=SELECTEDVALUE(Period[Period])))

     

     

    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.