Values from previous and current month, but with one month selected in slicer

[holodan95]

Hi,

Can you guys and gals tell me how to calculate with values both from current and previous month, while a single month is selected in a slicer?

I want to calculate the power usage of my client, for which I need their kwh position from the previous and current month.

My measure works by subtracting current month's last value from previous month's last value like:

Kwh position on 31/nov/2022 = 1200

Kwh position on 31/oct/2022 = 1000

Return value for 11th month is 200kwh. 

This measure is then built into another one with summarize in it, so I'm able to sumx all the values, to return a TOTAL like:

Measure =

Var _1 = SUMMARIZE(POWER_DATA,CLIENT_ID,MONTH,"usage",[usage_measure])

Return

Sumx(_1,[usage])

The problem starts when I only select one month in a slicer, because then my measure will return a blank value, as it cannot calculate with prev. Month's values. 

My first measure:

usage_measure =

var MAX_month_previous = CALCULATE(
MAX(Power_data[KWH entry]),
FILTER(ALLSELECTED(Power_data),
Power_data[Rank_Month]=SELECTEDVALUE(Power_data[Rank_Month])-1 && Power_data[Client_ID]=SELECTEDVALUE(Power_data[Client_ID]))) +0

var MAX_month_current = CALCULATE(
MAX(Power_data[KWH entry]),
FILTER(ALLSELECTED(Power_data),
Power_data[Rank_Month]=SELECTEDVALUE(Power_data[Rank_Month]) && Power_data[Client_ID]=SELECTEDVALUE(Power_data[Client_ID]))) +0
return
Max_month_current - Max_month_previous

Please help me with my project.

[Anonymous · Accepted solution]

Hi @holodan95 ,

Please try below steps:

1. create a measure with below dax formula

Usage_measure =
VAR cur_month =
    SELECTEDVALUE ( 'Power_data'[Month_of_entry] )
VAR cur_id =
    SELECTEDVALUE ( 'Power_data'[Client_ID] )
VAR tmp =
    FILTER (
        ALL ( 'Power_data' ),
        'Power_data'[Month_of_entry] = cur_month
            && 'Power_data'[Client_ID] = cur_id
    )
VAR tmp1 =
    FILTER (
        ALL ( 'Power_data' ),
        'Power_data'[Month_of_entry] = cur_month - 1
            && 'Power_data'[Client_ID] = cur_id
    )
VAR cur_max_date =
    MAXX ( tmp, [Day_of_entry] )
VAR pre_max_date =
    MAXX ( tmp1, [Day_of_entry] )
VAR val_cur_month =
    CALCULATE (
        MAX ( 'Power_data'[KWH entry] ),
        'Power_data'[Month_of_entry] = cur_month,
        'Power_data'[Day_of_entry] = cur_max_date,
        'Power_data'[Client_ID] = cur_id
    )
VAR val_pre_month =
    CALCULATE (
        MAX ( 'Power_data'[KWH entry] ),
        'Power_data'[Month_of_entry] = cur_month - 1,
        'Power_data'[Day_of_entry] = pre_max_date,
        'Power_data'[Client_ID] = cur_id
    )
VAR val = val_cur_month - val_pre_month
RETURN
    val

2. add a slicer with  'Power_data'[Month_of_entry], add a table visual with 'Power_data'[Client_ID] and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[holodan95 · Accepted solution]

Hi,

My working solution was to make a new table with groupby, using a variable summarize table.
I used table as a base for making the calculations in the matrix visual.

[Anonymous · Accepted solution]

Hi @holodan95 ,

Please try below steps:

1. create a measure with below dax formula

Usage_measure =
VAR cur_month =
    SELECTEDVALUE ( 'Power_data'[Month_of_entry] )
VAR cur_id =
    SELECTEDVALUE ( 'Power_data'[Client_ID] )
VAR tmp =
    FILTER (
        ALL ( 'Power_data' ),
        'Power_data'[Month_of_entry] = cur_month
            && 'Power_data'[Client_ID] = cur_id
    )
VAR tmp1 =
    FILTER (
        ALL ( 'Power_data' ),
        'Power_data'[Month_of_entry] = cur_month - 1
            && 'Power_data'[Client_ID] = cur_id
    )
VAR cur_max_date =
    MAXX ( tmp, [Day_of_entry] )
VAR pre_max_date =
    MAXX ( tmp1, [Day_of_entry] )
VAR val_cur_month =
    CALCULATE (
        MAX ( 'Power_data'[KWH entry] ),
        'Power_data'[Month_of_entry] = cur_month,
        'Power_data'[Day_of_entry] = cur_max_date,
        'Power_data'[Client_ID] = cur_id
    )
VAR val_pre_month =
    CALCULATE (
        MAX ( 'Power_data'[KWH entry] ),
        'Power_data'[Month_of_entry] = cur_month - 1,
        'Power_data'[Day_of_entry] = pre_max_date,
        'Power_data'[Client_ID] = cur_id
    )
VAR val = val_cur_month - val_pre_month
RETURN
    val

2. add a slicer with  'Power_data'[Month_of_entry], add a table visual with 'Power_data'[Client_ID] and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file. 

[holodan95]

Hi,

Please find below a sample data:

Client_ID	Day_of_entry	Month_of_entry	KWH entry	Rank_month	Rank_day-month
CL1	2022.11.05	11	0	1	1
CL1	2022.11.10	11	204	1	2
CL1	2022.11.15	11	325	1	3
CL1	2022.11.20	11	488	1	4
CL1	2022.11.25	11	708	1	5
CL1	2022.11.30	11	992	1	6
CL1	2022.12.05	12	1144	2	1
CL1	2022.12.10	12	1308	2	2
CL1	2022.12.31	12	1434	2	3
CL2	2022.11.01	11	0	1	1
CL2	2022.11.09	11	274	1	2
CL2	2022.11.17	11	693	1	3
CL2	2022.11.25	11	1124	1	4
CL2	2022.12.03	12	1485	2	1
CL2	2022.12.11	12	1933	2	2
CL2	2022.12.19	12	2293	2	3
CL2	2022.12.27	12	2706	2	4
CL2	2023.01.04	1	2929	3	1
CL2	2023.01.12	1	3347	3	2

Let's say we have a month slicer, with december selected. In that case I need to return

442 for CL1 (1434-992) and 1582 for CL2 (2706-1124).

My original measure is the following, which successfully calculates these values, when there is no month selected:

Usage_measure =

var MAX_month_previous = CALCULATE(

    MAX(Power_data[KWH entry]),

    FILTER(ALLSELECTED(Power_data),

    Power_data[Rank_Month]=SELECTEDVALUE(Power_data[Rank_Month])-1 && Power_data[Client_ID]=SELECTEDVALUE(Power_data[Client_ID]))) +0

var MAX_month_current = CALCULATE(

MAX(Power_data[KWH entry]),

    FILTER(ALLSELECTED(Power_data),

    Power_data[Rank_Month]=SELECTEDVALUE(Power_data[Rank_Month]) && Power_data[Client_ID]=SELECTEDVALUE(Power_data[Client_ID]))) +0

return

Max_month_current - Max_month_previous

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

 

[holodan95]

Hi,

Thank you, but I can only work with my measure, as the sample data I gave is very simple, free of faulty entries, and factors that I need to calculate with.

In some cases I have to ignore the last value, and calculate with the one which is prior to that etc..

Is there a way to adapt my measure or simplify it a way, so it does the same as yours?
I'll try and adapt some of your DAX like previousmonth and it out. But at first glance, it is to simple to work with my dataset.