Cumulative total with filter context

[Ratax]

Hello

i tried this function:

Cumulative 2 = CALCULATE (

DISTINCTCOUNT( 'PBI StickRoll'[ProductionItemBarcode] ),

FILTER (

ALLSELECTED('PBI StickRoll'[CreationDate - Copy]),

'PBI StickRoll'[CreationDate - Copy] <= MAX ('PBI StickRoll'[CreationDate - Copy] ))

)

That cumulates a given item created. I wanna add a filter that only filters todays date so added the statement

Cumulative 3 = CALCULATE (

DISTINCTCOUNT( 'PBI StickRoll'[ProductionItemBarcode] ),

FILTER (

ALLSELECTED('PBI StickRoll'[CreationDate - Copy]),

'PBI StickRoll'[CreationDate - Copy] <= MAX ('PBI StickRoll'[CreationDate - Copy] )),

'PBI StickRoll'[CreationDate] = TODAY()

)

But that doesn't seem to work.

Can anyone help

[Ratax · Accepted solution]

Contains a column of text and a date and time column

im trying to make a cumulative line chart of the rows in the productionitemstickroll text column, but only display the cumulative chart of the current date. When I use a visual filter (relative date) it works well

But when I try to add the daily filter context to themeasure (cumulative 3) the chart breaks.

Accumulated 3 - CALCULAR (

DISTINCTCOUNT( 'PBI StickRoll'[ProductionItemBarcode] ),

FILTER (

ALLSELECTED('PBI StickRoll'[CreationDate - Copy]),

'PBI StickRoll'[CreationDate - Copy] <- MAX ('PBI StickRoll'[CreationDate - Copy] )),

'PBI StickRoll'[CreationDate] to TODAY()

)

[Ratax · Accepted solution]

Contains a column of text and a date and time column

im trying to make a cumulative line chart of the rows in the productionitemstickroll text column, but only display the cumulative chart of the current date. When I use a visual filter (relative date) it works well

But when I try to add the daily filter context to themeasure (cumulative 3) the chart breaks.

Accumulated 3 - CALCULAR (

DISTINCTCOUNT( 'PBI StickRoll'[ProductionItemBarcode] ),

FILTER (

ALLSELECTED('PBI StickRoll'[CreationDate - Copy]),

'PBI StickRoll'[CreationDate - Copy] <- MAX ('PBI StickRoll'[CreationDate - Copy] )),

'PBI StickRoll'[CreationDate] to TODAY()

)

[v-jingzhang · Accepted solution]

Hi @Ratax 

If I understand correctly, you expect to display only today's cumulative values over time, right? If so, you could try the following measure:

Cumulative = 
CALCULATE (
    DISTINCTCOUNT ( 'PBI StickRoll'[ProductionItemBarcode] ),
    FILTER (
        ALLSELECTED ( 'PBI StickRoll' ),
        'PBI StickRoll'[CreationDate - Date] = TODAY ()
            && 'PBI StickRoll'[CreationDate - Time]
                <= MAX ( 'PBI StickRoll'[CreationDate - Time] )
    )
)

In my sample, I add two columns to separate the Date and Time. [CreationDate - Time] is used as Axis in the chart and [CreationDate - Date] is used to compare with TODAY() function in the measure. 

When you add the daily filter to the measure, you should take notice that fields on both sides of an equal sign should have the same data type. And don't forget && in FILTER() function when there are more than one filters.

For more details, please download the sample PBIX file. Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[Ashish_Mathur]

Hi,

Share some data, describe the question and show the expected result.