Drill up | Custom calendar | Last year

[Anonymous]

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))

[v-danhe-msft]

Hi @Anonymous,

Could you please share your sample file to have a test if possible?

Regards,

Daniel He

[Anonymous]

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.

[vanessafvg]

@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

[Anonymous]

@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?

[vanessafvg]

@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

[Anonymous]

@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