Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I've got troubles with LY (Last year) comparison in custom calendar table. There are 3 levels in calendar:
-Year
--Quarter
---Campaign
This measure down here doesn't work properly with Drill up to Year/Quarter level.
I would be gratefull for any help! 🙂
$Active Reps LY = VAR CurrentYear = SELECTEDVALUE('Calendar'[Year]) VAR CurrentCampaign= SELECTEDVALUE('Calendar'[Campaign short]) RETURN CALCULATE( [Active reps]; FILTER( ALL('Calendar'); 'Calendar'[Year]=CurrentYear-1 && 'Calendar'[Campaign short]=CurrentCampaign))
Hi @Anonymous,
Could you please share your sample file to have a test if possible?
Regards,
Daniel He
I guess it might not be necessary! 🙂 I changed formula into this:
Active Reps LY = VAR CurrentYear = SELECTEDVALUE('Calendar'[Year]) VAR CurrentCampaign = SELECTEDVALUE('Calendar'[Campaign short]) VAR CurrentQuarter= SELECTEDVALUE('Calendar'[Quarter]) RETURN IF( ISBLANK(CurrentCampaign) && ISBLANK(CurrentQuarter); CALCULATE( [Active reps]; FILTER( ALL('Calendar'); 'Calendar'[Year]=CurrentYear-1)); IF( ISBLANK(CurrentCampaign) && (CurrentQuarter)>0; CALCULATE( [Active reps]; FILTER( ALL('Calendar'); 'Calendar'[Year]=CurrentYear-1 && 'Calendar'[Quarter]=CurrentQuarter)); CALCULATE( [Active reps]; FILTER( ALL('Calendar'); 'Calendar'[Year]=CurrentYear-1 && 'Calendar'[Campaign short]=CurrentCampaign)) ))
But I still do not understand that the result of query changes depending on slicer choice.
@Anonymous it is working, because it brings back data at the lower level, but you expecting it to behave like a date when it doesn't have that date functionality, my suggestion is to use a proper date even if you just set it all to the first of the month. I could be wrong but thats just my opinion. your query is filtering it out at the higher level because it doesn't match the year month
Proud to be a Super User!
@vanessafvg appreciate your help, but I doubt it will work. Those time intelligence functions works with the full calendar table with no blanks. I can't relate my fact table to such a calendar because data I've got is aggregated to campaign. Maybe some other ideas?
@Anonymous
why not try https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax
or
https://docs.microsoft.com/en-us/dax/previousyear-function-dax
Proud to be a Super User!
@vanessafvg The reason is that I've got a custom calendar without dates. Functions that you mentioned require a datekey.
Campaign (whole number) | Campaign short (text) | Quarter (text) | Year (whole number) |
201901 | `01 | Q1 | 2019 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |