Code Help

[Anonymous]

I need help.

I have 2 visuals, one for 'this period' and one for 'previous period' but I need this to be dynamic.

I need a filter, that will give me Month/Quarter/Year.

When I select 'Month' I want it to give me Last month data in the first visual, and second one showing data from 2 months ago.
When I select 'Quarter' I want it to give me last 3 months (not including this month) and the 3 months prior in the second.
and so on...

with the overlapping periods I'm really struggling.

I'm using a standard date reference table so the key variable is data_refdate[d_refdate]

Happy for this to be in M, or DAX - or a combination.

Thanks in advance.

[amitchandak]

Refer, if this can help

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

[zoloturu]

@Anonymous ,

The technique 'Virtual relationships' in DAX can solve your task. I can help you with that. Please share how your visuals are look like. Data can be masked if needed. Or provide samples.

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

[Anonymous]

Thanks for your offer to help.

I've mocked up a simple version of what I have with notes on what I want to build. Never heard of 'virtual relationships' so excited to see what you have in mind.

https://files.fm/u/apxsuh8m 

[zoloturu · Accepted solution]

@Anonymous ,

In general, virtual relationships is an ability to propagate filters from unrelated column to another column. No physical relationship, but interaction still works. It can be applied to a calculated DAX measure/column/table.

So the solution would be next:

1. Create a table from values (using Enter Data menu):

2. Create a simple slicer based on the 'Periods'[Period] column. I used horizontal orientation, so it now looks like buttons:

3. Write two base measures, which will be reused later

TotalSales1 = SUM('Data 1'[Sales])

TotalSales2 = SUM('Data 2'[Sales 2])

3. Create measure, which will hold the number of month when year should start:

YearStartMonth = 9 //You can change to an other value anytime

4. Measure for sales from Data 1 table for Current Period would be:

TotalSales1 CurrPeriod =
VAR __period = MAX ( 'Periods'[Period] ) //Read current period from slicer
VAR __diff_months = [YearStartMonth] - 1

VAR __lastdate = LASTDATE ( 'Data 1'[d_refdate] ) //Read last date in context
VAR __month = MONTH ( __lastdate )
VAR __new_month =
    IF (
        __month > __diff_months,
        __month - __diff_months,
        __month + 12 - __diff_months
    )

VAR __temp = MOD ( __new_month, 3 )

VAR __date_from =
    SWITCH (
        __period,
        "Month", EOMONTH ( __lastdate, -1 ) + 1,
        "Quarter", SWITCH (
            __temp,
            1, EOMONTH ( __lastdate, -1 ) + 1,
            2, EOMONTH ( __lastdate, -2 ) + 1,
            0, EOMONTH ( __lastdate, -3 ) + 1
        ),
        "Year", EOMONTH ( __lastdate, 0 - __new_month ) + 1
    )

RETURN
    CALCULATE (
        [TotalSales1],
        FILTER (
            ALL ( 'Data 1'[d_refdate] ), //Remove all filters from this column
            'Data 1'[d_refdate] >= __date_from
                && 'Data 1'[d_refdate] <= __lastdate
        )
    )

5. Measure for sales from Data 1 table for Previous Period would be:

TotalSales1 PrevPeriod =
VAR __period = MAX ( 'Periods'[Period] )
VAR __diff_months = [YearStartMonth] - 1

VAR __lastdate = LASTDATE ( 'Data 1'[d_refdate] )
VAR __month = MONTH ( __lastdate )
VAR __new_month =
    IF (
        __month > __diff_months,
        __month - __diff_months,
        __month + 12 - __diff_months
    )

VAR __temp = MOD ( __new_month, 3 )
VAR __prev_period_offset = SWITCH ( __period, "Month", 1, "Quarter", 3, "Year", 12 )

VAR __date_from = 
    SWITCH (
        __period,
        "Month", EOMONTH ( __lastdate, -1 - __prev_period_offset ) + 1,
        "Quarter", SWITCH (
            __temp,
            1, EOMONTH ( __lastdate, -1 - __prev_period_offset ) + 1,
            2, EOMONTH ( __lastdate, -2 - __prev_period_offset ) + 1,
            0, EOMONTH ( __lastdate, -3 - __prev_period_offset ) + 1
        ),
        "Year", EOMONTH ( __lastdate, 0 - __new_month - __prev_period_offset ) + 1
    )
VAR __date_to =
    SWITCH (
        __period,
        "Month", EOMONTH ( __lastdate, 0 - __prev_period_offset ),
        "Quarter", SWITCH (
            __temp,
            1, EOMONTH ( __lastdate, 2 - __prev_period_offset ),
            2, EOMONTH ( __lastdate, 1 - __prev_period_offset ),
            0, EOMONTH ( __lastdate, 0 - __prev_period_offset )
        ),
        "Year", EOMONTH ( __lastdate, 12 - __new_month - __prev_period_offset )
    )

RETURN
    CALCULATE (
        [TotalSales1],
        FILTER (
            ALL ( 'Data 1'[d_refdate] ),
            'Data 1'[d_refdate] >= __date_from
                && 'Data 1'[d_refdate] <= __date_to
        )
    )

6. Repeat these two measures for a table 'Data 2'.

So if we select month then we receive next picture:

The same works for quarter:

And a year as well

Download a PBIX - here.

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

[Anonymous]

@zoloturu you've truly blown my mind with this!! really appreciate the effort you've put in to this.

I just need to play with it.

Thanks again.