Forum Discussion

mateoc15's avatar
mateoc15
Icon for Advocate II rankAdvocate II
10 months ago
Solved

Multiple Dates in Cube Table - relationship to Date table

I need help with relationships betwen multiple dates in a single table and a date dimension table.  I've found some articles, but nothing quite fits exactly what I'm looking for.  I want to be able t...
Need Help
  • RonaldBalza-943's avatar
    RonaldBalza-943
    10 months ago

    Here’s the refined version of your measure:

    Unique Members by Month =
    CALCULATE(
        SUM('App Cube'[unique_users]),
        USERELATIONSHIP('App Cube'[BeginningOfMonth], Dates[Date])
    )

     

    Unique Members by Year =
    CALCULATE(
        SUM('App Cube'[unique_users]),
        USERELATIONSHIP('App Cube'[BeginningOfYear], Dates[Date])
    )


    If you want dynamic switching (Month vs Year) in one measure, use:

    Unique Members Dynamic =
    SWITCH(
        TRUE(),
        SELECTEDVALUE(Dates[Granularity]) = "Month",
            CALCULATE(SUM('App Cube'[unique_users]), USERELATIONSHIP('App Cube'[BeginningOfMonth], Dates[Date])),
        SELECTEDVALUE(Dates[Granularity]) = "Year",
            CALCULATE(SUM('App Cube'[unique_users]), USERELATIONSHIP('App Cube'[BeginningOfYear], Dates[Date]))
    )
Anonymous's avatar
Anonymous
Not applicable
10 months ago

Hi mateoc15 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.


Thank you