YTG (Year to Go) Measure does NOT work with active relationship with Calendar

[Anonymous]

Hello 

@Ashish_Mathur - I adopted your solution with YTG - thank you!

@Greg_Deckler  - just bought your DAX Cookbook! Love it!

Will you please help me with the measure I wrote for YTG (Year to Go)?

Relationship : Calendar 1 - db_forecast INactive

Visial Matrix - it works fine!

Page Filters: Month- Year  from Calendar 

Measure: see below

YTG_Fct = VAR JrnlType = SWITCH(TRUE(),

MONTH(MAX('Calendar'[AccPer_#date]))=01, "YFR02",

MONTH(MAX('Calendar'[AccPer_#date]))=02, "YFR03",

MONTH(MAX('Calendar'[AccPer_#date]))=03, "YFR04",

MONTH(MAX('Calendar'[AccPer_#date]))=04, "YFR05",

MONTH(MAX('Calendar'[AccPer_#date]))=05, "YFR06",

MONTH(MAX('Calendar'[AccPer_#date]))=06, "YFR07",

MONTH(MAX('Calendar'[AccPer_#date]))=07, "YFR08",

MONTH(MAX('Calendar'[AccPer_#date]))=08, "YFR09",

MONTH(MAX('Calendar'[AccPer_#date]))=09, "YFR10",

MONTH(MAX('Calendar'[AccPer_#date]))=10, "YFR11",

MONTH(MAX('Calendar'[AccPer_#date]))=11, "YFR12")

RETURN

CALCULATE(

    SUM(db_forecast[Base Amount])/1000,

    DATESBETWEEN('Calendar'[AccPer_#date],

    NEXTMONTH('Calendar'[AccPer_#date]),

    DATE(YEAR(MAX('Calendar'[AccPer_#date])),12,31)),

    FILTER(db_forecast,db_forecast[Journal Type] = JrnlType),

    FILTER(db_forecast,db_forecast[Year]=MAX('Calendar'[Year])))

This Measure has to look forward (opposite to YTD) till year end, so I use DATESBETWEEN. 

I also have to use SWITCH to filter out the Journal Type as we load updated versions YFR02...YFR12 of forecast every month for 3 years ahead (from Next Month) , each YFR.. journal is valid from  a reporting month onward. Because of the three year outlook - I had to introduce FILTER(db_forecast,db_forecast[Year]=MAX('Calendar'[Year]) to remove 2021-2022 from the forecast version.

Challenge: 

If I switch on the ACTIVE relationship between Calendar  - The measure YTG_Fct does NOT work

[Anonymous]

You can tell it to use a specific relationship with the USERELATIONSHIP function 

https://docs.microsoft.com/en-us/dax/userelationship-function-dax#:~:text=USERELATIONSHIP%20uses%20e...

This way you can retain the active relationship for you other measures and use the inactive relationship for your new measure. 

Probably, something along the following lines of. If you ping me the PBIX file, I could get it working for you. 

YTG_Fct =
VAR JrnlType =
    SWITCH (
        TRUE (),
        MONTH ( MAX ( 'Calendar'[AccPer_#date] ) ) = 01, "YFR02",
        MONTH ( MAX ( 'Calendar'[AccPer_#date] ) ) = 02, "YFR03",
        MONTH ( MAX ( 'Calendar'[AccPer_#date] ) ) = 03, "YFR04",
        MONTH ( MAX ( 'Calendar'[AccPer_#date] ) ) = 04, "YFR05",
        MONTH ( MAX ( 'Calendar'[AccPer_#date] ) ) = 05, "YFR06",
        MONTH ( MAX ( 'Calendar'[AccPer_#date] ) ) = 06, "YFR07",
        MONTH ( MAX ( 'Calendar'[AccPer_#date] ) ) = 07, "YFR08",
        MONTH ( MAX ( 'Calendar'[AccPer_#date] ) ) = 08, "YFR09",
        MONTH ( MAX ( 'Calendar'[AccPer_#date] ) ) = 09, "YFR10",
        MONTH ( MAX ( 'Calendar'[AccPer_#date] ) ) = 10, "YFR11",
        MONTH ( MAX ( 'Calendar'[AccPer_#date] ) ) = 11, "YFR12"
    )
RETURN
    CALCULATE (
        SUM ( db_forecast[Base Amount] ) / 1000,
        DATESBETWEEN (
            'Calendar'[AccPer_#date],
            NEXTMONTH ( 'Calendar'[AccPer_#date] ),
            DATE ( YEAR ( MAX ( 'Calendar'[AccPer_#date] ) ), 12, 31 )
        ),
        FILTER ( db_forecast, db_forecast[Journal Type] = JrnlType ),
        FILTER ( db_forecast, db_forecast[Year] = MAX ( 'Calendar'[Year] ) ),
        USERELATIONSHIP ( 'Calendar'[AccPer_#date], db_forecast[AccPeriod] )
    )

Karlos. 

[Anonymous]

Hello 

Tried to add USERELATIONSHIP - it did NOT work unfortunately.

@Anonymous 

I will try to ping the file..

Thank you lots in advance,

Mira

[Anonymous]

Hello - not yet undresolved...
USERELATIONSHIP creates a relationship (even it is not a default from the data model)... 

I need a function that will deactivate for this measure the active datamodel relationship between the Calendar [Dates] and the datatable transaction dates. 
Although I don't fully understand why active relationship blocks the measure seeing future dates - Year to Go - till end of year... 

If anyone can help - it would be great!
Thank you lots

Mira

[Anonymous · Accepted solution]

I found the solution!

Wrapping the measure with another set of CALCULATE..., CROSSFILTER with option NONE deactivated the active relationship

Thank you all! Happy DAXing

https://community.powerbi.com/t5/Desktop/Deactivate-Relationship-in-a-measure/m-p/454772#M210708

Mira