Hiding Past and future dates for calculations in DAX if no Sales

[Harsh]

Hello @upwardD,

Thank You for posting your query onto the Forum.

Well the first thing is if you don’t have the data for the previous years than it’s point to keep the dates for those periods it will just unnecessarily bloat the size of the model. You can remove those periods from your date table. Below is the screenshot provided for the reference -

And for the future dates you can restrict the values by using the measures so that the last values are not repeated. Whilst in order to prevent the future dates you can also use the option of “IsAfterToday” and put it under the FIlter Pane section and un-check the option of “True”. As shown in the screenshot provided below -

I’m providing the link below from where you can copy the M Code of the Date table and paste it in your Advanced Editor and then use the given option as suggested above. Also I’m attaching the working of the PBIX file for the reference showing how you can restrict the values for future dates.

Hoping you find this useful.

Thanks and Warm Regards,
Harsh

Working File - Harsh.pbix (661.1 KB)

Extended Date Table (Power Query M function)

Hello Melissa, Awesome script, but I need it in the french language. Any suggestion for me ? I just need to translate the text nothing else. Should I just translate the text in “qoutes” and that should work ? Thanks Eric (Montreal, Canada)