Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

YTD Across multiple previous years based on latest date

Hi all,

 

I'm trying to replicate the action of this combined chart and slicer in a measure.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Essentially I want my measure to sum units to month from the start of the year, but to look for the maximum date (month) in the dataset and apply that time restraint to all previous years e.g. 2008 units = those between Jan and May 2008, 2009 units = those between Jan & May 2009 etc.

 

I can achieve this by defining the month I want my units to be summed i.e <= May, but my data is refreshed weekly so I'd like it to dynamically measure units each time we reach a new month in the data. 

 

It would be a SAMEPERIODLASTYEAR() but over all previous years, and I've tried out several variations of ALL() and FILTER() against a MAX([Month]), but it always only applies the MAX([Month]) against the final year in the dataset as all other years contain dates right up to 31st December.

 

Thanks for any insight anyone can provide.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HiAnonymous ,

     

    Based on the file provided by you I changed the measure

     

    1.  Max Month = Calculate(MONTH([Max Date]),ALL(Transactions))
     
    2. Transaction Count (YTD) = CALCULATE([Transaction Count],
                                 FILTER(ALL(Transactions[Month (Number)]),
                                   Transactions[Month (Number)]<=[Max Month] ))
     
     
    With these two changes the barchart on both sides is same.
     
    Check it out.
     
    Cheers
     
    CheenuSing

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-lili6-msft thanks for your reponse.

       

      Here is some sample data

       

      Each date represents a transaction. The final date in the series is in May 2019 and so I would like my measure [Transaction Count (YTD)] to show all previous years transactions only to May also. This is shown on the left hand charts which interact with the filter to show the desired result. My measure is in the right hand chart where i've tried to include a filter that would only count transactions that occured in a month less than the max month. I have tried to add in a max year too, but I'm not too sure where to include it

       

      Transaction Count (YTD) = CALCULATE([Transaction Count],FILTER(ALL(Transactions[Date]),Transactions[Date]<=[Max Date] && YEAR([Date])=[Max Year]))
      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        hi, Anonymous 

         My measure is in the right hand chart where i've tried to include a filter that would only count transactions that occured in a month less than the max month.

        What is your expected output? Do you mean that, if you select "Month (Number)" is 1-10, and the every year only calculate month before 5? because 2019 only have data until May, so other year should also calculate until may even if others year have data in other month.

         

        Best Regards,

        Lin