Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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):
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.
2/ I have 2 cards to display results of 2 mesures:
- 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]
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 simple
"Dates" table is my calendar table
"Period" table lists dates for each period option.
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
Can anyone help me pls?
Many thanks
@Cen,
For any time intelligence function, you could implement a custom DAX formula. Besides, this discussion might be helpful.
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))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |