Dax formula for creating rolling months with condition

[maatiash]

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

[v-xinruzhu-msft · Accepted solution]

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.

[maatiash]

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