2M Rolling Count, while respecting two separate date columns/conditions

[Anonymous]

Hi,

I'm having some trouble with creating a dax measure for my specific problem. 

I have a fact table containing claims related to specific orders (Claims[CNo]). The fact table have two important date columns; one of which is representing the production date of a product (Claims[PDate]) and the other which is representing the date where the customer has issued a claim related to the product sold (Claims[CDate]. Currently the claim date is connected to a calender dimension; Calendar[Date]. 

If a product is produced in january (as indicated in Claims[PDate]), I need to count claims occuring in january and february (as indicated by Claims[CDate]. I.e. i'm interested in a 2m rolling count of claims, assuming a specific production month. I'm planning to represent this data in a table, showing the stats for each month over several years. 

Thank you so much.

https://i.postimg.cc/13nhcKBq/picture.png 

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Why you not connect the PDate with the calendar table? Then you can use the following measure.

Measure =
CALCULATE (
    COUNTROWS ( Claims ),
    FILTER (
        Claims,
        Claims[CDate] >= MIN ( Calendar[Date] )
            && Claims[CDate] <= EOMONTH ( MAX ( calendar[Date] ), 1 )
    )
)

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

Best Regards,

Dedmon Dai

[parry2k]

@Anonymous you should have a relationship with calendar table on PDATE as well which will be inactive relationship and then you can add following measure to get the count:

Production Count = 
CALCULATE ( COUNTROWS ( YourTable ), USERELATIONSHIP ( Calendar[Date], YourTable[PDate] ) )

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi, and thanks for your help! 😊

I have included the suggestion into my PBI, but I am not sure how to connect all the dots. 

I am able to count claims occuring this or last month by applying:

Measure = Count(Claims[CNo])

Beginning = FIRSTDATE( DATESINPERIOD('Calendar'[Date], [Period End], -2, MONTH))

End = LASTDATE('Calendar'[Date])

2M = CALCULATE( [Measure],

DATESBETWEEN ('Calendar'[Date], [Period Start], [Period End]))

This essentially gives me the count of every claim this and last month. However, it counts independent of PDate, meaning that the production date could be in another month than last month (or another year for that matter). Any thoughts on how to make this connection to the above, i.e. force the model to only count claims this and last month - given that they were produced last month. 

Thanks!