Demo Challange Matrix

[PowerRobots99]

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?

[PowerRobots99]

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]

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]

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. 

[SamWiseOwl · Accepted solution]

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)

[PowerRobots99]

Hi @SamWiseOwl 

Thank you for reply, i appreciate your response on this, 

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)

With this measure. we can get number of Transactions completed between 5th Aug and 11 Aug by referring report date - 8/11/2024

However, as i mentioned in a single measure, Both conditions should meet,

1st Condition - 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

2nd Condition- Number of transactions completed between (5 Aug-11 Aug) by considering data with report date - 8/11/2024,

Both conditions should meet,

However, measure devised by you only take care of second condition.

[SamWiseOwl]

Hi @PowerRobots99 

Measures can only return 1 value, do you want them added together?

Can you make a mock up of what the answer will be please.

[SamWiseOwl]

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]

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 ?