Not able to calculate sum/count - Date Slider

[curious_monkey]

I have a excel sheet with data like below

my problem statement is very simple (with a small twist) - i want to create a small visual with a date slider and as i increase the date, it will show me the count (count of flag column) of all cities falling in that date range -- simple right.

now the problem part, the Cities whic have a INFRA code of UPGRADE should always be part of count and showuld not be effected by date slider: (Business Logic, we want to see the count of NEW cities added, UPGRADE means they existed before)

 To elleborate see below example:

CITIES ADDED UNTIL 12/3/2022 = 2 NEW + 8 UPGRADE = 10

CITIES ADDED UNTIL 12/5/2022 = 3 NEW + 8 UPGRADE = 11

CITIES ADDED UNTIL 12/8/2022 = 5 NEW + 8 UPGRADE = 13

So UPGRADE needs to be a constant, Count of UPBGRADE should only change, when there are new rows added with UPGRADE as a value.

Problem i am facing is that DATE SLIDER --> filter everything out by date filter and cannot seperate UPGRADE valued ROWS

I have tried to to seperate these two values by creating a measure using DAX (one measure taking count of values with only NEW, and one measure to count values with only UPGRADE) but that too get filtered by the DATE SLIDER??

what to do??

[curious_monkey]

@bolfri THANK YOU, THAT RESOLVED MY ISSUE

AS YOU CAN SEE ABOVE, I AM ABLE TO USE THE RUNNING TOTAL IN MY CARD - HOWEVER (THIS IS NOT A PROBLEM) THE UPGRADE CONSTANT FOR ME DOSENT SHOW TOTAL UPGRADE LIKE YOURS?

[bolfri]

Are you using a date field from that same table or do you use calendar for this?

[curious_monkey]

same table, no calender table used

[bolfri]

Can you share a pbix file with that issue? eg via https://wetransfer.com/ or I can give you my email adress. Before that: this is sample file from me: https://we.tl/t-230s9HmCcL check if this is what you were looking for.

[bolfri · Accepted solution]

Hi, 

Basic measure to sum flags:

Sum of flags = SUM('Sample'[FLAG])

To calculate upgrade constant you can use this measure:

Upgrade constant = CALCULATE([Sum of flags],ALL('Sample'),'Sample'[INFRA]="UPGRADE")

For new cities added it's:

Cities added = CALCULATE([Sum of flags],'Sample'[INFRA]="NEW")

Running totals:

Running total = 
var _maxdate = LASTDATE('Sample'[DATE])
var _upgrade = CALCULATE([Sum of flags],ALL('Sample'),'Sample'[INFRA]="UPGRADE")
var _new = CALCULATE([Sum of flags],'Sample'[INFRA]="NEW",FILTER(ALL('Sample'[DATE]),'Sample'[DATE]<=_maxdate))
return _upgrade + _new

Results: