Running total with Disconnected Date Table

[Mohan_GenZeon]

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.

[Mohan_GenZeon]

@v-kongfanf-msft 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.

[v-kongfanf-msft · Accepted solution]

Hi @Mohan_GenZeon ,

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.

[v-kongfanf-msft]

Hi @Mohan_GenZeon ,

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.