DAX: How to count based on dynamic time period?

Question

I have a data set as presented in the Sample 1 image below, and I need to generate a ratio of events per Serial Number (summary table as shown in the image).

The curve ball I got into here is the fact that the count of events ad sub events is monthly and the count of serial numbers is every 3 months, therefore the SN count needs to move the time period dynamically. In other words in March I need to divide the events from March by the count of SN's from January to March, while on April i need to divide the events from April by the count of SN's from February to April.

Any idea on how to do this?

Thanks in advance for all the help

I think it can be achieved, I mean a measure for calculating the rate you need can be created however that matrix at the right side in your image confuses me, is it your expected result? — alejandro zuleta, Nov 03 '16 at 20:34
The table/matrix to the right is the expected outcome indeed. The problem I'm having is specifically with the SN count... since it needs to be a dynamic 3 months count — Marcelo Aguilar, Nov 03 '16 at 20:52

score 0 · Answer 1 · answered Nov 10 '16 at 21:39

You will have to create two calculated columns in your table to get the month number and the month from the distinct count should be calculated.

MonthNumber =
SWITCH(
[Event Month],"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"Jun",6,
"Jul",7,"Ago",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12)

MonthFrom =
SWITCH([Event Month],"Jan",1,"Feb",1,"Mar",1,"Apr",2,"May",3,"Jun",4,"Jul",5,"Ago",6,
"Sep",7,"Oct",8,"Nov",9,"Dec",10)

Then you can create this measure that will give the SN distinct count of the last three months:

SN Count =
CALCULATE (
    DISTINCTCOUNT ( Event[SN] ),
    FILTER (
        ALL ( Event ),
        Event[MonthNumber] >= MAX ( [MonthFrom] )
            && [MonthNumber] <= MAX ( [MonthNumber] )
    )
)

Let me know if this helps.

DAX: How to count based on dynamic time period?

1 Answers1