Comparative period (nFiscalYear, nQuarter, nMonth)

[Pushp_Dalvi]

I need help in showing values based on comparative period selected. e.g When user selects "This Financial Year" and comparative period as '2 periods', then dashboard should display previous 2 Financial Year in addition to current financial year.

When "This Quarter" and comparative period = 2, then last 2 quarters along with current quarter is expected as output.

I tried relative date slicer instead of Comparative period, but it is not working in this case due to below 2 reasons-

1. Quarter is missing in relative date slicer

2. Our Fiscal year starts on April

3. Even applying relative date slicer, dashboard output is still being displayed as per selected period

Can anyone guide me how I can achive this?

[Pushp_Dalvi]

Here is the solution i have applied-

1. Date Table

2. Special Date table for predefined periods-

From this table I get predefined periods

3.Comparison period table

4. Interval table which defines what period to be compared when predefined period is selectede.g. When This financial year, Last Financial Year or Year to date is  selected then I do yearly comparison. When This Month, Last Month or Month To Date is selected, then compare by Months. Like wise for Quarters.

‘Interval table’ has relationship with ‘SpecialDates’ Table

Here order is unique number in specialdate table for predefined periods.

Note - Do Not link Datestable and SpecialDates Table.We need an independent, disconnected Dates table, which will serve to define the time frame we need for displaying values in bar chart visual (ref: https://towardsdatascience.com/override-date-filter-in-power-bi-743b9e8b9b2)

5. Now everything is in place. Using the Interval (Month, Year, Quarter) and selected comparison period (1,2…etc) I go back to previous dates and calculate the value (in my dashboard it’s Revenue’ calculation.

For previous revenue calculation, I used below logic

Comparative Revenue =

VAR _SelectedPeriod = SELECTEDVALUE(SpecialDates[Period])

VAR _ComparePeriod = SELECTEDVALUE('Comparison Periods'[Period])

VAR _SelectedInterval =  SELECTEDVALUE(Interval[Interval])

VAR _GoBackBy = If(_SelectedInterval = "Month",1*_ComparePeriod,

                    If(_SelectedInterval = "Quarter",3*_ComparePeriod,

                        iF(_SelectedInterval = "Year(FY)", 12*_ComparePeriod)))

VAR _StartDate = Calculate(FIRSTDATE(SpecialDates[Date]),

FILTER(

SpecialDates,

SpecialDates[Period] = _SelectedPeriod

))

VAR _EndDate = Calculate(LASTDATE(SpecialDates[Date]),

FILTER(

SpecialDates,

SpecialDates[Period] = _SelectedPeriod

))

VAR _StartYearMonthNumber = CALCULATE(VALUES(DateTable[YearMonthNumber]),DateTable[Date]=_StartDate)

VAR _PrevYearMonthNumber = _StartYearMonthNumber-_GoBackBy

VAR _EndYearMonthNumber = CALCULATE(VALUES(DateTable[YearMonthNumber]),DateTable[Date]=_EndDate)

VAR _CompareRevenue =

    CALCULATE(SUM(ContractRevenue[Revenue]),

FILTER(

DateTable,

DateTable[YearMonthNumber] >= _PrevYearMonthNumber &&

DateTable[YearMonthNumber] < _StartYearMonthNumber

)    )

Return  _CompareRevenue

Current Revenue calculation –

Current Revenue =

VAR _SelectedPeriod = SELECTEDVALUE(SpecialDates[Period])

VAR _StartDate = Calculate(FIRSTDATE(SpecialDates[Date]),

FILTER(

SpecialDates,

SpecialDates[Period] = _SelectedPeriod

))

VAR _EndDate = Calculate(LASTDATE(SpecialDates[Date]),

FILTER(

SpecialDates,

SpecialDates[Period] = _SelectedPeriod

))

VAR Revenue =

CALCULATE(SUM(ContractRevenue[Revenue]),

Filter(

DateTable,

DateTable[Date] >= _StartDate &&

DateTable[Date] <= _EndDate

)

)

Return Revenue

Before selecting compare period

After adding 1 period to compare –

In ‘Revenue by vertical’ and Revenue by Month’ I have already used split bar graph so I couldn’t display current and comparative side by side as I am displaying for clients and transaction type.

So for Revenue by vertical , I have used line and stacked graph where blue line represents previous period revenue

In Revenue by months, previous months gets displayed.

[v-yalanwu-msft]

Hi, @Pushp_Dalvi ;

You could create a flag measure and then apply it into filter.

flag =
VAR _DIFmon =
    DATEDIFF ( MAX ( [date] ), TODAY (), MONTH )
VAR _difqu =
    DATEDIFF ( MAX ( [date] ), TODAY (), QUARTER )
VAR _difday =
    DATEDIFF ( MAX ( [date] ), EOMONTH ( TODAY (), -1 ) + 1, DAY )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'period selection'[period selection] ),
        "This month",
            IF ( _DIFmon >= 0 && _DIFmon <= MIN ( 'comparison period'[period] ), 1, 0 ),
        "Last month",
            IF ( _DIFmon >= 1 && _DIFmon <= MIN ( 'comparison period'[period] ) + 1, 1, 0 ),
        "This Quarter",
            IF ( _difqu >= 0 && _difqu <= MIN ( 'comparison period'[period] ), 1, 0 ),
        "Last Quarter",
            IF ( _difqu >= 1 && _difqu <= MIN ( 'comparison period'[period] ) + 1, 1, 0 ),
        "This Financial Year",
            IF (
                MONTH ( TODAY () ) > 4,
                IF (
                    MAX ( [date] )
                        <= DATE ( YEAR ( TODAY () ) + 1, 3, 31 )
                        && MAX ( [date] )
                            >= DATE ( YEAR ( TODAY () ) - MIN ( 'comparison period'[period] ), 4, 1 ),
                    1,
                    0
                ),
                IF (
                    MAX ( [date] ) <= DATE ( YEAR ( TODAY () ), 3, 31 )
                        && MAX ( [date] )
                            >= DATE ( YEAR ( TODAY () ) - MIN ( 'comparison period'[period] ) - 1, 4, 1 ),
                    1,
                    0
                )
            ),
        "Month To Date",
            IF ( _difday >= 0 && _difday <= 3, 1, 0 )
    )

Then apply it into filter.

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pushp_Dalvi · Accepted solution]

Here is the solution I applied....

• Date Table

• Special Date table for predefined periods-

From this table I get predefined periods

• Comparison period table

1. Interval table which defines what period to be compared when predefined period is selected

e.g. When This financial year, Last Financial Year or Year to date is  selected then I do yearly comparison. When This Month, Last Month or Month To Date is selected, then compare by Months. Like wise for Quarters.

‘Interval table’ has relationship with ‘SpecialDates’ Table

Here order is unique number in specialdate table for predefined periods.

Note - Do not link DateTable and Special date table.We need an independent, disconnected Dates table, which will serve to define the time frame we need for displaying values in bar chart visual. (ref: https://towardsdatascience.com/override-date-filter-in-power-bi-743b9e8b9b2)

• Now everything is in place. Using the Interval (Month, Year, Quarter) and selected comparison period (1,2…etc) I go back to previous dates and calculate the value (in my dashboard it’s Revenue’ calculation.

For previous revenue calculation, I used below logic

Comparative Revenue =

VAR _SelectedPeriod = SELECTEDVALUE(SpecialDates[Period])

VAR _ComparePeriod = SELECTEDVALUE('Comparison Periods'[Period])

VAR _SelectedInterval =  SELECTEDVALUE(Interval[Interval])

VAR _GoBackBy = If(_SelectedInterval = "Month",1*_ComparePeriod,

                    If(_SelectedInterval = "Quarter",3*_ComparePeriod,

                        iF(_SelectedInterval = "Year(FY)", 12*_ComparePeriod)))

VAR _StartDate = Calculate(FIRSTDATE(SpecialDates[Date]),

FILTER(

SpecialDates,

SpecialDates[Period] = _SelectedPeriod

))

VAR _EndDate = Calculate(LASTDATE(SpecialDates[Date]),

FILTER(

SpecialDates,

SpecialDates[Period] = _SelectedPeriod

))

VAR _StartYearMonthNumber = CALCULATE(VALUES(DateTable[YearMonthNumber]),DateTable[Date]=_StartDate)

VAR _PrevYearMonthNumber = _StartYearMonthNumber-_GoBackBy

VAR _EndYearMonthNumber = CALCULATE(VALUES(DateTable[YearMonthNumber]),DateTable[Date]=_EndDate)

VAR _CompareRevenue =

    CALCULATE(SUM(ContractRevenue[Revenue]),

FILTER(

DateTable,

DateTable[YearMonthNumber] >= _PrevYearMonthNumber &&

DateTable[YearMonthNumber] < _StartYearMonthNumber

)    )

Return  _CompareRevenue

Current Revenue calculation –

Current Revenue =

VAR _SelectedPeriod = SELECTEDVALUE(SpecialDates[Period])

VAR _StartDate = Calculate(FIRSTDATE(SpecialDates[Date]),

FILTER(

SpecialDates,

SpecialDates[Period] = _SelectedPeriod

))

VAR _EndDate = Calculate(LASTDATE(SpecialDates[Date]),

FILTER(

SpecialDates,

SpecialDates[Period] = _SelectedPeriod

))

VAR Revenue =

CALCULATE(SUM(ContractRevenue[Revenue]),

Filter(

DateTable,

DateTable[Date] >= _StartDate &&

DateTable[Date] <= _EndDate

)

)

Return Revenue

Before selecting compare period

After adding 1 period to compare –

In ‘Revenue by vertical’ and Revenue by Month’ I have already used split bar graph so I couldn’t display current and comparative side by side as I am displaying for clients and transaction type.

So for Revenue by vertical , I have used line and stacked graph where blue line represents previous period revenue

In Revenue by months, previous months gets displayed.

[Pushp_Dalvi]

Thank you for your help. I did achieve my desired output with little different way. But surely, your inputs were kick start for me.

[ryan_mayu]

@Pushp_Dalvi 

could you pls provide the sample data and expected output?

[Pushp_Dalvi]

When I select any predefined period from 1st dropdown and add comparison period 1/2..., all graphs should show previous  periods for compariosn. 

say for example, i selecte 'This financial year' and compare period = 2..then visuals should include current financial year plus previous 2 years

If I select 'Last Month' and compare period = 3, then visual should include last month + previous 3 months

[Pushp_Dalvi]

Why I am not able to upload files here?

[ryan_mayu]

@Pushp_Dalvi 

you can try to use onedrive or googledrive to share your file

[Pushp_Dalvi]

https://drive.google.com/file/d/1yuNSCjOk47u3CkytLog8Wzkgvz-Oo7OV/view?usp=sharing

https://drive.google.com/file/d/191doKQ4lVODyIXdDL_9923Zsnan_iNcR/view?usp=sharing

Sharing Excel data and pbix

Thank You for helping!