Code Help

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