Forum Discussion

RolinMartis's avatar
RolinMartis
Icon for Helper II rankHelper II
3 years ago
Solved

Need help in replicating Tableau FIXED LOD Expression

Hi Everyone,

 

I have employee level data for 6 quarters

My requirement is to create a table which will have list of employees in the current quarter(FY2223Q2) and their Past 3 quarter score to be displayed

 

My Output should be as per Table below

 

Staff No.Staff NameEffective QuarterRegion / TerritoryFY2223 Q2 Approved Score%FY2223 Q1 Approved Score%FY2122 Q4 Approved Score%
1aaaFY2223 Q2JP77%85%71%
2bbbFY2223 Q2JP76%74%87%
3cccFY2223 Q2JP70%73%91%

 

It has only staff 1,2,3. Staff 4 is not there for FY2223Q2. so he is excluded. I need to build a measure to get Q2, Q1 and Q4 score

 

Can anyone help please. I am not sure how to upload the data or TWBX here.

 

it can be achieved using Fixed LOD in Tableau. But I am not sure how to do it in POwerBI

Tableau LOD Logic :

FY2223 Q2 = {Fixed Staff No, Effective Quarter : sum(If Effective Quarter = "FY2223 Q2" then Aproved Rate end) }

FY2223 Q1 = {Fixed Staff No, Effective Quarter : sum(If Effective Quarter = "FY2223 Q1" then Aproved Rate end) }

FY2122 Q4 = {Fixed Staff No, Effective Quarter : sum(If Effective Quarter = "FY2122 Q1" then Aproved Rate end) }

 

My Data looks like below

Staff No.Staff NameEffective QuarterRegion / TerritoryApproved Score%Approved Rate
1aaaFY2122 Q1JP118%173%
1aaaFY2122 Q2JP114%153%
1aaaFY2122 Q3JP100%100%
1aaaFY2122 Q4JP71%43%
1aaaFY2223 Q1JP85%71%
1aaaFY2223 Q2JP77%55%
2bbbFY2122 Q1JP102%105%
2bbbFY2122 Q2JP77%64%
2bbbFY2122 Q3JP70%70%
2bbbFY2122 Q4JP87%74%
2bbbFY2223 Q1JP74%48%
2bbbFY2223 Q2JP76%53%
3cccFY2122 Q1JP104%123%
3cccFY2122 Q2JP77%65%
3cccFY2122 Q3JP67%67%
3cccFY2122 Q4JP91%83%
3cccFY2223 Q1JP73%46%
3cccFY2223 Q2JP70%41%
4cccFY2122 Q1JP108%123%
4cccFY2122 Q2JP79%65%
4cccFY2122 Q3JP69%67%
4cccFY2122 Q4JP46%83%
4cccFY2223 Q1JP71%46%

 

Regards,

Rolin

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi RolinMartis ,

     

    Please try:

    Q1 = CALCULATE(MAX('Table'[Approved Score%]),'Table'[Effective Quarter]="FY2223 Q1")
    Q2 = CALCULATE(MAX('Table'[Approved Score%]),'Table'[Effective Quarter]="FY2223 Q2")
    Q4 = CALCULATE(MAX('Table'[Approved Score%]),'Table'[Effective Quarter]="FY2122 Q4")

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    RolinMartis , for fixed LOD refer:

    LOD- FIXED (Level of Details): https://youtu.be/hU-cVOwDCvY

     

    For Quarter data you can use TI

     

    QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
    Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))


    Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

    Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
    Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

    Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
    Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
    Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))

     

    or

     

    CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Year]="FY2223 Q2")))

     

     

    • RolinMartis's avatar
      RolinMartis
      Icon for Helper II rankHelper II
      3 years ago

      Hey  amitchandak 

       

      I tried the below formulae as my quarter filed is a text field. BUt its not giving me the desired output.

      Currently it is giving me the Minimum value in FY2223 Q1. BUt i also need it to pick the number for every employee.

       

       

      I would need a calculation which will work at 2 columns: for every Employee its should pick me their data for a specific quarter.

      I need to specify Staff number and Quarter in my condition.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi RolinMartis ,

         

        Please try:

        Q1 = CALCULATE(MAX('Table'[Approved Score%]),'Table'[Effective Quarter]="FY2223 Q1")
        Q2 = CALCULATE(MAX('Table'[Approved Score%]),'Table'[Effective Quarter]="FY2223 Q2")
        Q4 = CALCULATE(MAX('Table'[Approved Score%]),'Table'[Effective Quarter]="FY2122 Q4")

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum