Forum Discussion

PowerRobots99's avatar
PowerRobots99
Helper II
2 years ago
Solved

Demo Challange Matrix

Hello Friends,

Please refer link with DemoChallange.pbix file.  

https://drive.google.com/drive/folders/1S4iOi_u6aBEK0bJOko_Zvw29SES8jjqS?usp=drive_link 

Now, our challange is to display numbers in matrix visual as shown.

 

Now, lets understand logic for the calculation - (Lets take exmaple for Location- A and Report date - 8/11/2024)

 

We need to find out how many transactions were scheduled between (5 Aug-11 Aug) from previous report week's data to report numbers for - 8/11/2024,

To do this, we need to select report date - 8/4/2024, and from "TransScheduleCompletionDate" we need to find out how many transactions are scheduled from (5 Aug-11 Aug), so, if for example 57 transactions were scheduled, we need to report this number under 8/11/2024.

 

 Is there any way to achieve this?

Need Help
Show and Tell
Tips and Tricks
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago

    Hi PowerRobots99  I think I have Friday fatigue...

    Completed date =
     var selDate = LASTDATE(Data[ReportDate]) --Capture current Report Date
      var lastweek = DATEADD(selDate,-7,DAY)
     var filDate =
     CALCULATETABLE( --modify filters
        FILTER(Data, [TransActualCompletionDate] > lastweek &&[TransActualCompletionDate] <= selDate)
        --only return rows after last week (excl 04) and to this week (inc 11/08)
        ,Data[ReportDate] = selDat--keep current report filter 
        )
     RETURN
     COUNTROWS(filDate)

8 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    2 years ago

    Hi PowerRobots99 

    You need to change the report filter using Calculate and then add a filter based on the completion date using Filter (there are other methods). Create a measure like this:

     

    Scheduled date =
     var selDate = LASTDATE(Data[ReportDate]) --Capture current Report Date
     var lastweek = DATEADD(selDate,-7,DAY)
     var filDate =
     CALCULATETABLE( --modify filters
        FILTER(Data, [TransScheduleCompletionDate] > lastweek &&[TransScheduleCompletionDate] <= selDate)
        --only return rows after last week (excl 04) and to this week (inc 11/08)
        ,Data[ReportDate] = lastweek--replace current report filter with last week
        )
     RETURN
     COUNTROWS(filDate)

     

     
    • PowerRobots99's avatar
      PowerRobots99
      Helper II
      2 years ago

      Hi SamWiseOwl,

       

      I was trying to set up drill through for this table, but my filters are not passing through as we have modified the filters inside "Scheduled Date" measure.

       

      Please let me know if there is any way to resolve this ?

  • PowerRobots99's avatar
    PowerRobots99
    Helper II
    2 years ago

    Thank you SamWiseOwl, Excellent approach, really appreciate your response. 

     

    Could you please let me know if there is any way to find out number of transactions that were scheduled between (5 Aug-11 Aug) AND also completed between (5 Aug-11 Aug),

     

    To find out scheduled transactions between (5 Aug-11 Aug), we referred data that had uploaded on report date - 8/4/2024 in earlier step,

     

    However, To find out completed transactions between (5 Aug-11 Aug), we have to refer data that have been loaded on report date - 8/11/2024.  

     

     

     

    • SamWiseOwl's avatar
      SamWiseOwl
      Super User
      2 years ago
      Completed date =
       var selDate = LASTDATE(Data[ReportDate]) --Capture current Report Date
       
       var filDate =
       CALCULATETABLE( --modify filters
          FILTER(Data, [TransScheduleCompletionDate] > lastweek &&[TransScheduleCompletionDate] <= selDate)
          --only return rows after last week (excl 04) and to this week (inc 11/08)
          ,Data[ReportDate] = selDat--keep current report filter 
          )
       RETURN
       COUNTROWS(filDate)
       
      Sounds like you just remove the last week filter
      • PowerRobots99's avatar
        PowerRobots99
        Helper II
        2 years ago

        I believe that is not correct,

         

        We have to find out number of transactions that were scheduled from 5 Aug to 11 Aug by considering data with report date- 8/4/2024

        AND

        also completed between (5 Aug-11 Aug) by considering data uploaded on 8/11/2024,

        Both conditions should meet,

         

        Now, to find out completed transactions from 5th and 11 Aug, we have to use "TransActualCompletionDate"

         

        In above measure, we haven't done that.