Need help with month over month calculation

[ashwinkolte]

Item	Subitem	Date	Cost
Item1	Subitem1	30-Jun-23	100
Item1	Subitem2	30-Jun-23	200
Item1	Subitem1	31-May-23	400
Item1	Subitem2	31-May-23	600
Item2	Subitem1	31-May-23	500

I have inoput data given above . Task is to calculate cost saving i.e June month - May month for the items and its sub items

My challenge is this : The input data does not have data for June month for item2 . But it has data for May . Since there is data for May , I want June cost to be considered 0 and cost  saving over May calculated as (0-500). The overall output should be as below in a matrix form

Item	Subitem	30-Jan-23	31-May-23	Month over month cost saving
Item1	Subitem1	100	400	-300
Item1	Subitem2	200	600	-400
Item2	Subitem1	-	500	-500
total		300	1,500	-1200

In other words if current moth is june and there is not record for june but records are there for may then June should be considered 0 

[Anonymous · Accepted solution]

Hi @ashwinkolte 

You can refer to the following example.

1.Create two tables

Table 2 = SUMMARIZE(ALLSELECTED('Table'),[Item],[Subitem ])

Table 3 = SUMMARIZE('Table',[Date])

2.Create two measures

Sum_cost =
VAR a =
    FILTER (
        'Table',
        [Item]
            IN VALUES ( 'Table 2'[Item] )
                && [Subitem ]
                    IN VALUES ( 'Table 2'[Subitem ] )
                        && EOMONTH ( [Date], 0 ) = EOMONTH ( SELECTEDVALUE ( 'Table 3'[Date] ), 0 )
    )
RETURN
    IF ( COUNTROWS ( a ) > 0, CALCULATE ( SUM ( 'Table'[Cost] ), a ), 0 )

over month cost saving =
[Sum_cost]
    - CALCULATE (
        SUM ( 'Table'[Cost] ),
        EOMONTH ( 'Table'[Date], 0 ) = EOMONTH ( SELECTEDVALUE ( 'Table 3'[Date] ), -1 ),
        'Table'[Item] IN VALUES ( 'Table 2'[Item] ),
        'Table'[Subitem ] IN VALUES ( 'Table 2'[Subitem ] )
    )

3.Put the field of table 2 to the row  ,  the field of table3 to column, the two measures to value in matrix visual.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @ashwinkolte 

You can create a new table

Table 2 =
VAR a =
    SUMMARIZE (
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2023, 12, 31 ) ),
            "Eomonth", EOMONTH ( [Date], 0 )
        ),
        [Eomonth]
    )
VAR b =
    SUMMARIZE ( 'Table', [Product], [category] )
VAR c =
    ADDCOLUMNS (
        GENERATE ( a, b ),
        "flag",
            VAR _count =
                COUNTROWS (
                    FILTER (
                        'Table',
                        [Product] = EARLIER ( 'Table'[Product] )
                            && [category] = EARLIER ( 'Table'[category] )
                            && YEAR ( [Date] ) = YEAR ( [Eomonth] )
                    )
                )
            RETURN
                IF ( _count > 0, 1, 0 )
    )
RETURN
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( c, [flag] = 1 ),
            'Table'[Product],
            'Table'[category],
            [Eomonth]
        ),
        "Sales",
            VAR _lookup =
                MAXX (
                    FILTER (
                        'Table',
                        [Product] = EARLIER ( 'Table'[Product] )
                            && [category] = EARLIER ( 'Table'[category] )
                            && EOMONTH ( [Date], 0 ) = EARLIER ( [Eomonth] )
                    ),
                    [Sales]
                )
            RETURN
                IF ( _lookup <> BLANK (), _lookup, 0 )
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ashwinkolte · Accepted solution]

Absolutely Brilliant ! Thanks a lot @Anonymous 

[Anonymous · Accepted solution]

Hi @ashwinkolte 

You can refer to the following example.

1.Create two tables

Table 2 = SUMMARIZE(ALLSELECTED('Table'),[Item],[Subitem ])

Table 3 = SUMMARIZE('Table',[Date])

2.Create two measures

Sum_cost =
VAR a =
    FILTER (
        'Table',
        [Item]
            IN VALUES ( 'Table 2'[Item] )
                && [Subitem ]
                    IN VALUES ( 'Table 2'[Subitem ] )
                        && EOMONTH ( [Date], 0 ) = EOMONTH ( SELECTEDVALUE ( 'Table 3'[Date] ), 0 )
    )
RETURN
    IF ( COUNTROWS ( a ) > 0, CALCULATE ( SUM ( 'Table'[Cost] ), a ), 0 )

over month cost saving =
[Sum_cost]
    - CALCULATE (
        SUM ( 'Table'[Cost] ),
        EOMONTH ( 'Table'[Date], 0 ) = EOMONTH ( SELECTEDVALUE ( 'Table 3'[Date] ), -1 ),
        'Table'[Item] IN VALUES ( 'Table 2'[Item] ),
        'Table'[Subitem ] IN VALUES ( 'Table 2'[Subitem ] )
    )

3.Put the field of table 2 to the row  ,  the field of table3 to column, the two measures to value in matrix visual.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ashwinkolte]

Dear @Anonymous 

Thankyou so much ! It worked . Really appreciate it . Thanks a lot. May I take the liberty to ask for more help if you dont mind 🙂 

I have a input table as below

Product	category	Sales	Date
P1	C1	100	31-Jan-23
P1	C2	200	30-Apr-23
P2	C1	100	30-Jun-22

I want an output table as given below. i.e for whichever months OF THE YEAR there are no sales it should insert row with 0 sales for EACH product category combination .

Product	category	Sales	Date
P1	C1	100	31-Jan-23
P1	C1	0	28-Feb-23
P1	C1	0	31-Mar-23
P1	C1	0	30-Apr-23
P1	C1	0	31-May-23
P1	C1	0	30-Jun-23
P1	C1	0	31-Jul-23
P1	C1	0	31-Aug-23
P1	C1	0	30-Sep-23
P1	C1	0	31-Oct-23
P1	C1	0	30-Nov-23
P1	C1	0	31-Dec-23
P1	C2	0	31-Jan-23
P1	C2	0	28-Feb-23
P1	C2	0	31-Mar-23
P1	C2	200	30-Apr-23
P1	C2	0	31-May-23
P1	C2	0	30-Jun-23
P1	C2	0	31-Jul-23
P1	C2	0	31-Aug-23
P1	C2	0	30-Sep-23
P1	C2	0	31-Oct-23
P1	C2	0	30-Nov-23
P1	C2	0	31-Dec-23
P2	C1	0	31-Jan-22
P2	C1	0	28-Feb-22
P2	C1	0	31-Mar-22
P2	C1	0	30-Apr-22
P2	C1	0	31-May-22
P2	C1	100	30-Jun-22
P2	C1	0	31-Jul-22
P2	C1	0	31-Aug-22
P2	C1	0	30-Sep-22
P2	C1	0	31-Oct-22
P2	C1	0	30-Nov-22
P2	C1	0	31-Dec-22

Appreciate if you can help with this 

[Anonymous · Accepted solution]

Hi @ashwinkolte 

You can create a new table

Table 2 =
VAR a =
    SUMMARIZE (
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2023, 12, 31 ) ),
            "Eomonth", EOMONTH ( [Date], 0 )
        ),
        [Eomonth]
    )
VAR b =
    SUMMARIZE ( 'Table', [Product], [category] )
VAR c =
    ADDCOLUMNS (
        GENERATE ( a, b ),
        "flag",
            VAR _count =
                COUNTROWS (
                    FILTER (
                        'Table',
                        [Product] = EARLIER ( 'Table'[Product] )
                            && [category] = EARLIER ( 'Table'[category] )
                            && YEAR ( [Date] ) = YEAR ( [Eomonth] )
                    )
                )
            RETURN
                IF ( _count > 0, 1, 0 )
    )
RETURN
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( c, [flag] = 1 ),
            'Table'[Product],
            'Table'[category],
            [Eomonth]
        ),
        "Sales",
            VAR _lookup =
                MAXX (
                    FILTER (
                        'Table',
                        [Product] = EARLIER ( 'Table'[Product] )
                            && [category] = EARLIER ( 'Table'[category] )
                            && EOMONTH ( [Date], 0 ) = EARLIER ( [Eomonth] )
                    ),
                    [Sales]
                )
            RETURN
                IF ( _lookup <> BLANK (), _lookup, 0 )
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.