Count instances only if categorised the same for previous 3 months

[Fusion_Tomo]

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 contains the details for every mobile service (number, plan type etc,). Each month the individual mobile services are categorised according to their data usage as either being Zero/Under, Excessive, or Nominal as per the Data Use Category column.

I would like to recreate the below stacked bar chart that counts the number of mobile services in the Zero/Under & Excessive categories for the past three months. By this I mean:

• A mobile service will be counted in the Excessive category only if it has been categorised as Excessive for the given month AND the previous 2 months
• A mobile service will be counted in the Zero/Under category only if it has been categorised as Zero/Under for the given month AND the previous 2 months
• The remainder shall be counted as Nominal no matter what their classification for the given and previous 2 months is.

I am going to put this chart in a dashboard that is filterd by a single selection of MM-YY.

To summarise: a mobile service has to be classified as either Zero/Under or Excessive for three consectutive months up to and including the selected month or else it is counted as Nominal.

Any help constructing the neccessary measure(s) to accomplish this would be much appreciated.

Regards,

Steven.

[Shaurya]

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]

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.