topic Dax formula for creating rolling months with condition in DAX Commands and Tips

Dax formula for creating rolling months with condition

maatiash — Wed, 18 Jan 2023 12:58:44 GMT

Hi Everyone 😊

I'm trying to create a Rolling Month field in DAX with a condition. Note: the examples below are the screenshots from Excel to show what output I want to get in Power BI. Hope it is clear 🙂

The conditon is described below:

When the field Aligned is filtered to No, then the Rolling Month is calculated for each country for the most current available month. It is shown below:

When field is filtered to "Yes" then it is aligning all fields to the month where Sales are available and calculating month accordingly. You may see below an example:

I also attached the PBIX file, where you can check details:

Any help much appreciated, thank you!

Link to the PBIX file is here:

https://drive.google.com/drive/folders/1CGU2XIhiC2K2kWd9984tTxbWmX3G42uA?usp=share_link

Regards,
Matias

Re: Dax formula for creating rolling months with condition

Anonymous — Thu, 19 Jan 2023 06:25:42 GMT

Hi maatiash

You can create a mesure:

Rolling month = "M"&COUNTROWS(FILTER(ALLSELECTED(CountryData),[Country]=MAX([Country])&&MONTH(CountryData[Reporting Date])>=MONTH(MAX([Reporting Date]))))

and set the following filter in visual filter:

Output:

Best Regards!

Yolo Zhu

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

Re: Dax formula for creating rolling months with condition

maatiash — Thu, 19 Jan 2023 10:47:13 GMT

Hi Yolo,

Thank you for your help! Please let me to test, so I'll let you know if it works 🙂

Thanks and Regards,

Matias