Forum Discussion

Regular Visitor

2 years ago

Need some help with DAX

Hello, I am new to power BI, I have the following columns I need to create a DAX that will show me %of SDL1 in a 12 month rolling list for each Submission Type. I have tried to create meas...

vpzim

Regular Visitor

2 years ago

Hello Yulia,

My apologies, I don't think I was clear enough.

I am trying to make a 12month rolling metric - % of submissions that had an SD month over month. previously in excel when I did the calculations it was:

submitted in a month with an SD/submitted in a month (12 month metric)

I have a lot of sensitive data so can't send pbix, but this what the chart looked like in excel

vpzim

Regular Visitor

2 years ago

I was able to get a calculation formed:

Rolling 12-month SD% =

VAR CurrentDate = MAX('Registrations Tracker'[Submitted].[Date])

VAR RollingWindow = DATESINPERIOD('Registrations Tracker'[Submitted].[Date], CurrentDate, -12, MONTH)

VAR TotalSD = SUMX(FILTER('Registrations Tracker', 'Registrations Tracker'[SDL 1].[Date] IN RollingWindow), 'Registrations Tracker'[Class III SD])

VAR TotalCount = SUMX(FILTER('Registrations Tracker', 'Registrations Tracker'[Submitted].[Date] IN RollingWindow), 'Registrations Tracker'[Class III Count])

RETURN

DIVIDE(TotalSD, TotalCount)

Though, this is only consider month to month - I need the formula to take into account 12 months of rolling data, not month to month.