topic Need some help with DAX in DAX Commands and Tips

Need some help with DAX

vpzim — Tue, 16 Jul 2024 20:45:15 GMT

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 measures for this and divide, but I am not getting anywhere.

Any help would be appreciated.

Thank you.

Re: Need some help with DAX

Anonymous — Wed, 17 Jul 2024 04:30:16 GMT

Hi vpzim

I don't quite understand what the 12-month rolling list looks like? Is it based on the selected date of the slicer?

Could you please provide some sample data and the expected results based on the sample data? That would be very helpful. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Here's a simple sample I created, I hope it helps.

Measure = VAR _submitted = CALCULATE(COUNT('Table'[Submitted]), ALLEXCEPT('Table', 'Table'[Type])) VAR _SDL1 = CALCULATE(COUNT('Table'[SDL1]), FILTER(ALLEXCEPT('Table', 'Table'[Type]), [SDL1] <> BLANK())) RETURN DIVIDE(_SDL1, _submitted)

Output:

Best Regards,
Yulia Xu

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

Re: Need some help with DAX

vpzim — Wed, 17 Jul 2024 15:26:27 GMT

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

Re: Need some help with DAX

vpzim — Thu, 18 Jul 2024 13:02:32 GMT

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.