cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Drill up | Custom calendar | Last year

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! Smiley Happy

 

 

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

tmp.PNG

6 REPLIES 6
Super User
Super User

Re: Drill up | Custom calendar | Last year

@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

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Anonymous
Not applicable

Re: Drill up | Custom calendar | Last year

@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`01Q12019
Super User
Super User

Re: Drill up | Custom calendar | Last year

@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


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Anonymous
Not applicable

Re: Drill up | Custom calendar | Last year

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

Community Support Team
Community Support Team

Re: Drill up | Custom calendar | Last year

Hi @Anonymous,

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Drill up | Custom calendar | Last year

I guess it might not be necessary! Smiley Happy 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.

 

tmp1.PNGtmp3.PNGtmp4.PNG