Running total missing values

[Mak_Leo]

Hi,

I'm facing an unusual issue that I'm not able to solve.

I've created a running total on a table that which increment every year.
In the same project I've also a counter for every yea which is working fine.

The code for the annual counter is this:

Count =

VAR _Model = SELECTEDVALUE(Table[Model])

VAR _Count = CALCULATE(SUM(Table[Counter]),Table[Type]="TYPE1" && Table[Model] = _Model )

RETURN

IF(ISBLANK(_Count ),0,_Count )

The source of the rolling total intead is this:

CountSum =

VAR _Model = SELECTEDVALUE(Table[Model])

VAR _Count = CALCULATE (SUM (Table[Counter]),FILTER(ALL(Table),Table[Date] <= MAX(Table[Date]) && Table[Type]="TYPE1" && Table[Model] = _Model ))

RETURN

_Count

What I get from those two measures is the following :

the runnign total is working properly but for unknow reason return nothing in 2020 and 2023 when the count is zero.

Have you any suggetion to solve this ?

Thx

[Ashish_Mathur · Accepted solution]

Hi,

This pattern should work

1. Create a Calendar Table with a calculated column formula to extract the Year
2. Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table
3. To your visual, drag Year from the Calendar Table
4. Write these measures

Count = CALCULATE(SUM(Table[Counter]),Table[Type]="TYPE1")

Running count = calculate([count],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

Hope this helps.

[Mak_Leo]

Hi,

many thanks, the proposed solution worked perfectly.

In ordet to better understanding, could you provide some more detail for the usage of Datesbetween and minx ?

Thanks again

[Ashish_Mathur]

You are welcome.  Please read up on these yourself.

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.