Forum Discussion

Mak_Leo's avatar
Mak_Leo
New Member
3 years ago
Solved

Running total missing values

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

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    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.

4 Replies

  • foodd's avatar
    foodd
    Community Champion
    3 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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's avatar
      Mak_Leo
      New Member
      3 years ago

      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