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

 

Any help would be appreciated.

Thank you. 

3 Replies

  • 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.