Forum Discussion

Fusion_Tomo's avatar
Fusion_Tomo
Icon for Advocate I rankAdvocate I
3 years ago

Count instances only if categorised the same for previous 3 months

Hey guys, I have the following fact table that records mobile phone billings as at the last day of each month: The table is connected to a Calendar table and a Mobile Service dim table that c...
Need Help
Shaurya's avatar
Shaurya
Icon for Memorable Member rankMemorable Member
3 years ago

Hi Fusion_Tomo,

 

You can use the following DAX Formula:

 

Category = IF(CALCULATE(DISTINCTCOUNT('Table'[Data Usage Category],FILTER(DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH)))=1
&& 'Table'[Data Usage Category]<>"Nominal",
MAX('Table'[Data Usage Category]),
"Nominal")

 

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: How to Export Telemetry Data from Azure IoT Central into Power BI

Fusion_Tomo's avatar
Fusion_Tomo
Icon for Advocate I rankAdvocate I
3 years ago

Thank you for taking the time to respond but that doesn't work - Power BI will not allow me to enter the 'Table'[Data Usage Category]<>"Nominal" condition and I'm not sure the logic of the formula is suited to the problem.