DAX How to display previous month and current month's value in a particular period on the same axis?

[inna_sysco]

Hi community, 
Could someone help me to correct the DAX measure to display previous months' values in a particular period?

I already have few measures to calculate the current month's value and measure to calculate the previous month's value.

Also, I have a measure to calculate the sum of Table[VALUE] for the current month, and for the line, where "Chart section" = "Opening" it calculates the sum of the previous month.
There is one matrix with different months in columns and values from Config[CHART_SECTION] in rows.
At the intersection of a column and a row should be displayed the value for the current month ( in the column for January 2021, the value for January 2021)
But for the row where Config[CHART_SECTION] = "Opening" should be displayed the previous month value (for ex: for January 2021, should be displayed value for December 2020)

My problem is to combine all values in the same table on one axis period...

When I use the column with the original date (TABLE[Daytime]) it shows correct values just in line for the "previous month" (Opening) , all other values are wrong.. and I'm not able to configure the period (it display all months that have data)

When I use the custom column (Table[Format Daytime]) - I'm able to change the period, but values for the line "Opening" - BLANK...

Can someone help me to edit or change my measures to have all data on the same date axis?

My .pbx is attached.

[v-yingjl]

Hi @inna_sysco ,

Try to modify the formula like this:

Result =
// VAR currentdate =
//     SELECTEDVALUE ( 'TABLE'[DAYTIME] )
// VAR previousdate =
//     EDATE ( currentdate, -1 )
VAR start_date =
    CALCULATE ( MIN ( Calendar_start[Date] ), ALLSELECTED ( Calendar_start[Date] ) )
VAR end_date =
    CALCULATE ( MAX ( Calendar_end[Date] ), ALLSELECTED ( Calendar_end[Date] ) )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( CONFIG[CHART_SECTION] ) = "Opening",
            CALCULATE (
                SUM ( 'TABLE'[VALUE] ),
                FILTER (
                    ALL ( 'TABLE' ),
                    'TABLE'[DAYTIME] = EDATE ( MIN ( 'TABLE'[DAYTIME] ), -1 )
                )
            ),
        SELECTEDVALUE ( CONFIG[CHART_SECTION] ) = "Lifted",
            CALCULATE (
                SUM ( 'TABLE'[VALUE] ),
                'TABLE'[DAYTIME] >= start_date
                    && 'TABLE'[DAYTIME] <= end_date
            ),
        SELECTEDVALUE ( CONFIG[CHART_SECTION] ) = "Production",
            CALCULATE (
                SUM ( 'TABLE'[VALUE] ),
                'TABLE'[DAYTIME] >= start_date
                    && 'TABLE'[DAYTIME] <= end_date
            ),
        SELECTEDVALUE ( CONFIG[CHART_SECTION] ) = "Closing",
            CALCULATE (
                SUM ( 'TABLE'[VALUE] ),
                'TABLE'[DAYTIME] >= start_date
                    && 'TABLE'[DAYTIME] <= end_date
            ),
        SELECTEDVALUE ( CONFIG[CHART_SECTION] ) = "Adjustment",
            CALCULATE (
                SUM ( 'TABLE'[VALUE] ),
                'TABLE'[DAYTIME] >= start_date
                    && 'TABLE'[DAYTIME] <= end_date
            ),
        BLANK ()
    )

Attached the file in the below, hopes it could help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[inna_sysco]

Hi @v-yingjl , thank you very much for help!
your solution works well when I need to display a date range that contains data...
but in such cases (as on the photos below) it shows extra data...


Any ideas why?

Best wishes, Inna

[amitchandak]

@inna_sysco .I check the file.

In case you need 2 date filter the you need 2 disconneted date tables

refer , you can use month slicer in place date table 

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

or with one LOV/Slicer

 Better to use time intelligence

examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

[inna_sysco]

@amitchandak , Thanks for the answer, but this is not what I'm looking for...
Two separate slicers is a requirement for the task and also I already have two detached data table where I pick up values...
My question is - how to combine 2 measures on the same axis.

[amitchandak]

@inna_sysco , if you need same time axis value (say Dec 2020 as a month, in this case, will only happen when you have a common date table

There you can merge Nov 2020, Dec 2020 together using this month or last month formula.

In this case, it will not combine in terms of months. You can show daytime as common axis but it will not merge values across month