Comparing the change of week in different categories under the same table and columns

[Anonymous]

Hi all,

I have this table below and I am using a dropdown list to select different report dates. I am trying to compare if there are changes of week within the same order number. For example, we can see that for PO2, the week changes from 1 to 7 in the corresponding report date (18-Feb and 2-Mar).

I want to display the Count of Order number and the sum of Qty in which that the same order number changes its week given two dates. This is a sample display that I want to show:

May I know whether any DAX measures would help this calculation?

Many Thanks.

[Anonymous]

HI @Anonymous,

You can try to use the following measure formulas if they are suitable for your requirement:

Count of Order =
VAR currDate =
    MAX ( Table[Date] )
VAR weekstart =
    currDate - WEEKDAY ( currDate, 2 ) + 1
VAR currGroup =
    SELECTEDVALUE ( NewTable[Group] )
VAR week_offset =
    LOOKUPVALUE ( NewTable[lookup_table], NewTable[Group], currGroup )
VAR prevDate =
    DATE ( YEAR ( weekstart ), MONTH ( weekstart ), DAY ( weekstart ) - week_offset * 7 )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( Table[Order Number] ) ),
        FILTER (
            ALL ( Table ),
            YEAR ( [ReportDate] ) = YEAR ( prevDate )
                && WEEKNUM ( [ReportDate], 2 ) = WEEKNUM ( prevDate, 2 )
        )
    )

Sum of Qty =
VAR currDate =
    MAX ( Table[Date] )
VAR weekstart =
    currDate - WEEKDAY ( currDate, 2 ) + 1
VAR currGroup =
    SELECTEDVALUE ( NewTable[Group] )
VAR week_offset =
    LOOKUPVALUE ( NewTable[lookup_table], NewTable[Group], currGroup )
VAR prevDate =
    DATE ( YEAR ( weekstart ), MONTH ( weekstart ), DAY ( weekstart ) - week_offset * 7 )
RETURN
    CALCULATE (
        SUM ( Table[Qty] ),
        FILTER (
            ALL ( Table ),
            YEAR ( [ReportDate] ) = YEAR ( prevDate )
                && WEEKNUM ( [ReportDate], 2 ) = WEEKNUM ( prevDate, 2 )
        )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin,

Thanks for your reply. I tried to incorporate the measures into the dashboard however it seems like it doesn't show the groups of the lookup_value table, instead it sums up into a single category. Are there anything else I need to add? I tried to add the group to the legend as well but it returns an error. The below picture is for your reference.

Thanks.

Best,

Arthur

[Anonymous]

HI @Anonymous,

You need to use the 'week group' field axis of your chart, then measure can get corresponding results based on the current group.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @ghoshabhijeet,

Thanks for your reply. For the X-axis of the charts, I categorize the week changes by incorporating vlookup function in excel as follows:

For value equal or bigger than 4, I categorize it as more than plus 3 weeks and a similar approach for week changes equal or less than -4.

[ghoshabhijeet]

@Anonymous  Thanks for sharing the categories. Would you be able to share some sample data ?

[Anonymous]

Hey @ghoshabhijeet,

Yes, I can provide some sample data in the below link:

PBI Sample Data

Thanks.

[ghoshabhijeet]

@Anonymous  What are the categories/buckets which you are showing in the X-Axis of the charts ?