Calculate Data for Last 3,6 & 12 Months based on Month Selection

[Anonymous]

Hi,

I have a Date table as fact table and a few other dimension tables related to it with Date column. I have to create multiple visuals. I want to select one month (for instance Nov 2019) in "Filters on all page" and want some visual to show data for last 3 months (i.e. Sep, Oct, Nov), some to show data for 6 months and so on. I don't know how to do it. I am very new to Power BI. 

PS - I have reports working if the respective date selection is made on their page. I want to make the date selection common to all the pages even for those visuals which are showing data for past 3/6/12 months.

Thanks in advance.  🙂

Saurabh

[v-gizhi-msft]

hi,

According to your description, please try these measures.

1) Calculate data for last 3 months

Measure =

VAR las3 =

    EDATE ( MAX ( 'Table18'[Date] ), -3 )

VAR sell3 =

    YEAR ( las3 ) * 100

        + MONTH ( las3 )

VAR sele =

    YEAR ( MAX ( Table18[Date] ) ) * 100

        + MONTH ( MAX ( Table18[Date] ) )

VAR cal =

    YEAR ( MAX ( Table11[Date] ) ) * 100

        + MONTH ( MAX ( 'Table11'[Date] ) )

RETURN

IF ( cal <= sele && cal > sell3, 1, 0 )

Sales-Test =        CALCULATE(SUM(Table11[Sales]),FILTER(Table11,[Measure]=1))

And it shows as follows.

2) Calculate data for one month you select

Measure 3 =

var a = MONTH(MAX(Table11[Date]))

var b = MONTH(MAX(Table18[Date]))

return

IF(a=b,1,0)

Sales-Common = CALCULATE(SUM(Table11[Sales]),FILTER(Table11,[Measure 3]=1))

And it shows as follows.

Here is my test pbix file.

pbix 

If you still have questions about this, please for free to let me know.

Best Regards,

Giotto Zhi

[Anonymous]

@v-gizhi-msft 
Thank you very much for the reply.
But This is not what I want. I am able to solve the problem when there is just one table connected to the date table. Issue arises when multiple tables are used in the same visual. Please refer to the PBIX file.

PBIX FILE

Looking forward to your reply.

PS -  I was thinking if we can use a staging Date table to use in slicer/filter for user selection input which is not related to any table and then somehow if we can pass that selection to the main Date table and use that there to create 3 dynamic conditional columns each for 1Month, 3 Months & 6 Months with just "Y" & "N" in them and thereafter we can use those conditional columns in filter pane to filter out original Date table accordingly?

[v-gizhi-msft]

hi,

According to your description, I create two tables to test:

Please try these measures:

Last3Months =

VAR selecteddate =

    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )

VAR l =

    EOMONTH ( CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ), -2 )

RETURN

    CALCULATE (

        SUM ( Fact[Sales] ),

        FILTER (

            ALL ( 'Fact' ),

            'Fact'[Date] <= selecteddate

                && 'Fact'[Date] >= DATE ( YEAR ( l ), MONTH ( l ), 1 )

        )

)

Last6Months =

VAR selecteddate =

    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )

VAR l =

    EOMONTH ( CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ), -5 )

RETURN

    CALCULATE (

        SUM ( Fact[Sales] ),

        FILTER (

            ALL ( 'Fact' ),

            'Fact'[Date] <= selecteddate

                && 'Fact'[Date] >= DATE ( YEAR ( l ), MONTH ( l ), 1 )

        )

    )

Last12Months =

VAR selecteddate =

    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )

VAR l =

    EOMONTH ( CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ), -11 )

RETURN

    CALCULATE (

        SUM ( Fact[Sales] ),

        FILTER (

            ALL ( 'Fact' ),

            'Fact'[Date] <= selecteddate

                && 'Fact'[Date] >= DATE ( YEAR ( l ), MONTH ( l ), 1 )

        )

    )

By ‘Date’ slicer, it shows:

By this way, when you choose one date, the three visuals will display different data order by last 3/6/12 months.

It should be noticed that this way can only show the sum of last 3/6/12 months instead of detailed data of each month.

Here is my test pbix file.

pbix 

If you still have questions about this, please for free to let me know.

Best Regards,

Giotto Zhi