Forum Discussion

nbitabc's avatar
nbitabc
Icon for Helper I rankHelper I
5 years ago

YTD Metric does not reset

Hello,

 

I am pretty new to PowerBI and trying to create Year to Date Measure. Have created YTD calculation for measures based on Month DIM - Year field, which works fine as long as Year field is included in table. But doesn't work if users do not select Year in the table. Is there a way to fix this and not have users to select Year everytime they user YTD metrics ? My current dataset is on monthly level and doesn't have any dates field to use TOTALYTD function. 

 

YTD Measure definition:

 

Direct Premium Written YTD = 
                               CALCULATE(SUM([Direct Premium Written]), 
                                                                  FILTER(ALLEXCEPT(ACCOUNTING_MONTH,ACCOUNTING_MONTH[Year]),
                                                                       ACCOUNTING_MONTH[Year Month] <= MAX(ACCOUNTING_MONTH[Year Month])))

 

 
 
General Comment
Tips and Tricks
Tutorial Requests

1 Reply

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    Hi nbitabc ,

     

    Measures are based on context so the fact that you explictitly call out the year on the calculation using the all except you are forcing that value to be consider in the filter, so if you do not have the year in your visualization the context for that specific column are all the years of the table so the values do no get reset.

     

    In this case you can make use of a different measure for example TOTALYTD or instead of using the ALLEXCEPT make the use of the KEEPFILTERS or making a filter with and defining the MAX date and the minimum has the current year and month.

     

    Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.