Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Running total with Disconnected Date Table

Hello Team,

 

I have a situation where i am trying to calcualte the running total between Date_master table to Eligmember table. but there is no any relaitonship created as this eligmember table is a scd type 2 table and having inactive relaitonship.

Here is a sample model with a few tables:


Here is the DAX formula for one measure: based on the toggle selection

 

RegMembers_Metric_During =
SWITCH(TRUE(),
    SELECTEDVALUE(PBI_ELIGIBILITY_STATUS[STATUS]) = "At End of Selected Date Range",[RegMembers_Elig_End],
    SELECTEDVALUE(PBI_ELIGIBILITY_STATUS[STATUS])= "During Selected Date Range",[RegMembers_Elig_During])

 

RegMembers_Elig_End =
VAR _MinDate = MIN(PBI_MASTER_CALENDAR[DATE_VALUE])
VAR _MaxDate = MAX(PBI_MASTER_CALENDAR[DATE_VALUE])
RETURN                
CALCULATE(DISTINCTCOUNT(PBI_ELIGMEMBER[GUID]),
            KEEPFILTERS(PBI_ELIGMEMBER[REGISTRATION_DATE] <> BLANK()),
                KEEPFILTERS(PBI_ELIGMEMBER[REGISTRATION_DATE] >= _MinDate &&
                        PBI_ELIGMEMBER[REGISTRATION_DATE] <= _MaxDate),
                KEEPFILTERS(PBI_ELIGMEMBER[BIEFFECTIVEDATE] <= _MaxDate &&
                        PBI_ELIGMEMBER[BIENDDATE] >= _MaxDate))  

RegMembers_Elig_During =
VAR _MinDate = MIN(PBI_MASTER_CALENDAR[DATE_VALUE])
VAR _MaxDate = MAX(PBI_MASTER_CALENDAR[DATE_VALUE])
RETURN                
CALCULATE(DISTINCTCOUNT(PBI_ELIGMEMBER[GUID]),
            KEEPFILTERS(PBI_ELIGMEMBER[REGISTRATION_DATE] <> BLANK()),
                KEEPFILTERS(PBI_ELIGMEMBER[REGISTRATION_DATE] >= _MinDate &&
                        PBI_ELIGMEMBER[REGISTRATION_DATE] <= _MaxDate),
                KEEPFILTERS(PBI_ELIGMEMBER[BIEFFECTIVEDATE] <= _MaxDate &&
                        PBI_ELIGMEMBER[BIENDDATE] >= _MinDate))  


Here [MaxDate] and [MinDate] are come from the Master calendar table. And the Master calendar table has no direct relationship with Eligimember table.

See the final output of the measure

 


Please provide suggestions for any dax queries.

Thanks
Mohan V.

General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    To modify your DAX expression to use the GUID column (string data type) instead of the amount column, you can use the COUNTROWS function to count the number of rows that match the condition. Such like below formula:

    RunningTotal_ = 
VAR _MinDate = MIN(PBI_MASTER_CALENDAR[DATE_VALUE])
VAR _MaxDate = MAX(PBI_MASTER_CALENDAR[DATE_VALUE])
RETURN
COUNTROWS(
    FILTER(
        PBI_ELIGMEMBER,
        PBI_ELIGMEMBER[REGISTRATION_DATE] >= _MinDate &&
        PBI_ELIGMEMBER[REGISTRATION_DATE] <= _MaxDate &&
        PBI_ELIGMEMBER[BIEFFECTIVEDATE] <= _MaxDate &&
        PBI_ELIGMEMBER[BIENDDATE] >= _MinDate
    )
)

     

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    Maybe you can modify your formula like below:

    RunningTotal = 
VAR _MinDate = MIN(PBI_MASTER_CALENDAR[DATE_VALUE])
VAR _MaxDate = MAX(PBI_MASTER_CALENDAR[DATE_VALUE])
RETURN
SUMX(
    FILTER(
        PBI_ELIGMEMBER,
        PBI_ELIGMEMBER[REGISTRATION_DATE] >= _MinDate &&
        PBI_ELIGMEMBER[REGISTRATION_DATE] <= _MaxDate &&
        PBI_ELIGMEMBER[BIEFFECTIVEDATE] <= _MaxDate &&
        PBI_ELIGMEMBER[BIENDDATE] >= _MinDate
    ),
    PBI_ELIGMEMBER[AmountColumn] 
)

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Anonymous Thanks for the reply.

     

    I have to use the GUID column here and i do not have any amount column in my table.

    And FYI, guid is STRING data type column.

     

    Please guide me on how i can have this expression work with STRING datatype column.

     

    Apreciate your time and efforts on it.

     

    Thanks,

    Mohan V.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Anonymous ,

       

      To modify your DAX expression to use the GUID column (string data type) instead of the amount column, you can use the COUNTROWS function to count the number of rows that match the condition. Such like below formula:

      RunningTotal_ = 
VAR _MinDate = MIN(PBI_MASTER_CALENDAR[DATE_VALUE])
VAR _MaxDate = MAX(PBI_MASTER_CALENDAR[DATE_VALUE])
RETURN
COUNTROWS(
    FILTER(
        PBI_ELIGMEMBER,
        PBI_ELIGMEMBER[REGISTRATION_DATE] >= _MinDate &&
        PBI_ELIGMEMBER[REGISTRATION_DATE] <= _MaxDate &&
        PBI_ELIGMEMBER[BIEFFECTIVEDATE] <= _MaxDate &&
        PBI_ELIGMEMBER[BIENDDATE] >= _MinDate
    )
)

       

       

      Best Regards,
      Adamk Kong

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.