Measure that calculates total sales up to today for a selected year

[mtrevisiol]

Hi everyone.

I've got a measure that calculates total sales from a FACT_MOV table. 

Then, I've got a slicer with the years (from 2011 to 2021). My goal would be to get the total sales from 1st January to today (17th June) related to the year selected in the slicer.

For example, if I select 2015, I would like to get Total sales from 01/01/2015 to 17th June 2015. Do you know what is the current formula to use in the measure?

Thank you.

[v-stephen-msft]

Hi @mtrevisiol ,

First create a seperate(no relationship) year table by dax.

Table 2 = DISTINCT('Table'[Date].[Year])

Second create the measure

Filtered Date = CALCULATE(MAX('Table'[Date]),FILTER('Table',YEAR('Table'[Date])=SELECTEDVALUE('Table 2'[Year])&&[Date]<=DATE(SELECTEDVALUE('Table 2'[Year]),MONTH(TODAY()),DAY(TODAY()))))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mtrevisiol]

Thank you, but still doesn't work. I get "empty" even if a year is selected.

[v-stephen-msft · Accepted solution]

Hi @mtrevisiol ,

If possible, please share your data with me, or provide some dummy data similar to your source data.

I'll try my best to help you.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mtrevisiol]

Thank you for the help,  @v-syr . There was a problem with the interactions between visualizations I wasn't aware of.

[Fowmy]

@mtrevisiol 

You have a dates table, if not create one and link to your fact table. Then, you can use the TOTALYTD or TOTALYTD   functions

https://docs.microsoft.com/en-us/dax/totalytd-function-dax

https://docs.microsoft.com/en-us/dax/datesytd-function-dax

[mtrevisiol]

Thank you for the suggestion, but how can I use TOTALYTD for a year that is not the current one, but the selected one?