Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cen
Frequent Visitor

Coexistence of Period slicer and Previous year result

Hello,

 

I don't know a lot yet about Power BI and am stucked with my problem for weeks without a solution. 

I've been reading lots of posts and articles on the internet which gave me some ideas but apparently not enough...

 

1/  I have 2 slicers to toggle with ("Period" slicer and "Date" slicer):

  • The "Period" slicer is a drop down list containing 6 options: Last Week, Last Month, Last 3 Months, Last 12 Months, Month To Date and Year To Date)

Note: Last Month, Last 3 Months, Last 12 Months are always previous completed months. If today is feb 19th 2018 then Last Month = whole month of jan 2018.

Period_Slicer.PNG

 

 

 

 

 

 

 

 

  • The "Date" slicer is a basic custom period slicer:
    Date_Slicer.PNG

 

 

 

  • Then i have a toggle button set up with bookmarks so users can choose between those 2 slicers.

Toggle.PNG

 

2/ I have 2 cards to display results of 2 mesures:

  • The 1st mesure: SelectedPeriod = IF(ISFILTERED('REF'[OtherFilter]); SUM('DATA'[Var1]); SUM('DATA'[Var2]))

- I have another slicer in by report using column 'REF'[OtherFilter] but it's not relevant to my question.

If this irrelevant filter is used then the measure takes the sum of column DATA'[Var1], otherwise it takes the sum of column 'DATA'[Var2]

  • The 2nd mesure: PreviousYear = CALCULATE([SelectedPeriod];SAMEPERIODLASTYEAR('DATES'[Date]))

This mesure gives the sum on the same period of previous year.

 

For example if i selected in my period slicer "Last Month", then SelectedPeriod gives me result of Jan 2018 and PreviousYear gives me Jan 2017

 

3/ Model

I build my model by taking inspiration from this article in order to link my calendar table with the period slicer table:

http://analyticsavenue.com/power-bi-timeperiod-slicer-for-last-7-dayslast-30-days/

Forgive me for changing some table/column names from my original data model, just to keep the question simpleData_model.png

 

 

 

 

 

 

 

 

"Dates" table is my calendar table

"Period" table lists dates for each period option.


Period_table.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

4/ Problem

The 2 mesures work fine with Date slicer but mesure PreviousYear gives (Blank) when selecting on the Period slicer.

For example, when Period slicer is shown (toggle to relative period) and Date slicer is hidden, if i select Last Month:

- SelectedPeriod gives the right result of Jan 2018

- PreviousYear gives (Blank) instead of Jan 2017 result

 

Thank you very much for your help,

Cen

3 REPLIES 3
Cen
Frequent Visitor

Can anyone help me pls?

Many thanks

v-chuncz-msft
Community Support
Community Support

@Cen,

 

For any time intelligence function, you could implement a custom DAX formula. Besides, this discussion might be helpful.

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

Hi @v-chuncz-msft

What do you mean by "implement a custom DAX formula"? Should i create my own formula to resolve my problem? 

I'm rather new to PowerBI and don't know a lot of formulas yet but if it's the only way then i will look into it...

 

I also had a look the other discussion and tried to re-write the "PreviousYear" as below but it didn't work. This time "PreviousYear" gives me the sum of 'DATA'[Var2] of the selected period instead of the sum of SUM('DATA'[Var1] of selected period on previous year...

PreviousYear =
VAR MinSelDate = MIN('DATES'[Date])
VAR MaxSelDate = MAX('DATES'[Date])
RETURN
CALCULATE([SelectedPeriod]; filter(all('DATA'); 'DATA'[Date] >= MinSelDate && 'DATA_DIG_FAKE'[Date] <= MaxSelDate))

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.