Running total not working with a date column but works with the integer representation of that date

[CarlSagan]

Hi all, I have a data model that consists of asset records that are my transactions. This table has columns for the amount, date of transaction, and an integer representation of that date. Process period date is a date datatype and is in the format of mm/dd/yyyy. 

//Relationship with columns

// Each process period date has an integer ppid value

Each process period happens at the end of each month and when i do the standard running total formula that uses calculate and filters based off of the date in the filter context with the max date in the filter context, it just gives me the amount for the month selected in the slicer. Below is one of the running total queries using the process period date. In the card visuals i have the running total using this measure, the running total using the measure with ppid, and the total removing the filter context on date and ppid.

it's like power bi can't recognize the process period date as a date column. i've casted the column as a date, i've created a date table and used that to no avail. any help would be great!

[mark_endicott · Accepted solution]

@CarlSagan - Use DAX like this, and then make sure your visual is using the column from the date table:

CALCULATE (
    [assets_total],
    FILTER (
        ALLSELECTED ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

If this works for you, please mark it as the solution, it helps with visibility for others with the same issue. 

[FreemanZ]

hi @CarlSagan ,

try like:

measure =

CALCULATE(

    [],

    FILTER(

       ALL(assets),

       ....

    )

[CarlSagan]

you can't use an empty array as the value for table in calculate. i don't know if you were indirectly saying just use the measure for total that i'm using. but this is definitely not the solution

[Anonymous · Accepted solution]

Hi @CarlSagan ,

I think ALL() function should achieve your goal.

asset_running_total_process_date = 
CALCULATE(
    [assets_total],
    FILTER(
        ALL(assets),
        assets[process_period_date]<=MAX(assets[process_period_date])
    ))

Or you can try REMOVEFILTERS() Function.

asset_running_total_process_date = 
    CALCULATE(
    [assets_total],
    REMOVEFILTERS(assets[ppid]),
        assets[process_period_date]<=MAX(assets[process_period_date])
    )

You can download my sample file to learn more details.

If this reply still couldn't help you solve your issue, please share a sample file with us and show us the result you want.

I think we need to know the calculation logic of [assets_total], in my sample, I just sum the value.

Best Regards,
Rico Zhou

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

[mark_endicott · Accepted solution]

@CarlSagan - Use DAX like this, and then make sure your visual is using the column from the date table:

CALCULATE (
    [assets_total],
    FILTER (
        ALLSELECTED ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

If this works for you, please mark it as the solution, it helps with visibility for others with the same issue. 

[CarlSagan]

still giving me the same result. i swear it's a data type issue or something. it doesn't make any sense because i've tried all techniques using just the date table, just the date column. i don't think it's having trouble using the value in a condition though so that means it's recognizing it's a date. it's just giving me the value as if there was no special dax, just the sum

[mark_endicott]

@CarlSagan - Things to check:

1) Is Auto DateTime enabled in the settings - if so disable.

2) Have you marked your Date table as a Date Table - if so do this. 

3) Your screenshot of the assests table shows tran_date is recognised as a date field yet process_process_period_date is not - This needs investigating if 1) does not work

Once you have checked all these things, if you still have the issue you'll need to share the file, or some sample data & more screenshots of my code in use, and the visual for me to investigate further.