Forum Discussion

vpzim's avatar
vpzim
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...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

  • vpzim's avatar
    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's avatar
      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.