Forum Discussion

fabdata1207's avatar
fabdata1207
Regular Visitor
3 years ago
Solved

Simple Running Count

Hi all

 

I need to do a simple running total with sort by date

 

in the below sample I need to create a measure as the same of Running Total and that counts the CODE and sort based on Date exactly like the Running Total column

 

CODEDateRunning Total
B1/07/20211
A4/07/20212
A7/07/20212
D9/07/20213
C9/07/20214
F15/07/20215
F20/07/20216
F20/07/20216
E20/07/20216
Total31/07/20216

 

 

is that possible ?

 

Thanks all

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    fabdata1207 

    you can try this

     

    Measure = 
VAR _total=CALCULATE(DISTINCTCOUNT('Table'[CODE]),FILTER(all('Table'),'Table'[Date]<=max('Table'[Date])))
return if (ISFILTERED('Table'[CODE]),_total, DISTINCTCOUNT('Table'[CODE]))

     

     

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    3 years ago

    fabdata1207 

    you can try this

     

    Measure = 
VAR _total=CALCULATE(DISTINCTCOUNT('Table'[CODE]),FILTER(all('Table'),'Table'[Date]<=max('Table'[Date])))
return if (ISFILTERED('Table'[CODE]),_total, DISTINCTCOUNT('Table'[CODE]))

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    There are duplicate combinations of CODE and Date i.e. F and 20/7/2021.  Is this by design?  I ask because when these 2 columns will be dragged to the visual, you will see only 1 row for F and 20/7/2021.  Is there any other column you have in your dataset which makes the CODE and Date combination unique?  If not, then would you want a calculated column solution instead of a measure?