Running total vs blank dates in vs calendar

[Anonymous]

Hi,

i'm having troubles with following issue. I need to report by time how many of salesmen is eligbile (that means he's got more than YYY $$ sales per day). The table below shows data for 1 salesman. I wanna build a area chart based on "If eligible" measure for all salesmen.

KK id	Campaign	Data	Running total	If eligible
xxxxxxxx		18.10.2018		0
xxxxxxxx		19.10.2018		0
xxxxxxxx		20.10.2018		0
xxxxxxxx	201815	21.10.2018	331.38	1
xxxxxxxx	201815	22.10.2018	411.34	1
xxxxxxxx		23.10.2018		0
xxxxxxxx		24.10.2018		0
xxxxxxxx		25.10.2018		0
xxxxxxxx	201815	26.10.2018	579.28	1
xxxxxxxx		27.10.2018		0

The point is - my measure doesn't work with blank dates. I mean there are:

1. Calendar with dates

2. Invoices with sales

When there is no invoice for particular date my running total is blank.

The measure looks like this:

Running total sales measure test =

CALCULATE (
SUM('Sales & Prizes'[sls]);
FILTER (
ALL('Calendar');
'Calendar'[Data] <= MAX('Sales & Prizes'[day])
&&
'Calendar'[Campaign long] = SELECTEDVALUE('Sales & Prizes'[Campaign]) 
))

I would be very grateful for your help...!!

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,
Dale

[Anonymous]

@Dale,

I'm still working on it. I will mark answers and respond as soon as I solve problems and will show you my solution.

Best regards

Update:

I marked the solution. The key was changing "ALL ( 'Calendar' )" into "ALL ( 'Calendar'[Date] )". Really appreciate!

[v-jiascu-msft]

Hi @Anonymous,

Everything seems good. Try this one, please. The dates in the visual should be from the 'Calendar' table.

Running total sales measure test =
CALCULATE (
    SUM ( 'Sales & Prizes'[sls] );
    FILTER (
        ALL ( 'Calendar' );
        'Calendar'[Data] <= MAX ( 'Calendar'[Data] )
            && 'Calendar'[Campaign long] = SELECTEDVALUE ( 'Sales & Prizes'[Campaign] )
    )
)

Best Regards,
Dale

[Anonymous]

Hi @Dale,

I really much appreciate your help!

..but it still doesn't work properly...

Salesman.Salesman IDCalendar.SalesCampaignCalendar.DateRunning total sales measure

XXXX	201815	21.10.2018 00:00	331
XXXX	201815	22.10.2018 00:00	411
XXXX	201815	26.10.2018 00:00	579

If I tick "Show items with no data" on table visual I get:

Salesman.Salesman IDCalendar.SalesCampaignCalendar.DateRunning total sales measure

XXXX	201815	18.10.2018 00:00
XXXX	201815	19.10.2018 00:00
XXXX	201815	20.10.2018 00:00
XXXX	201815	21.10.2018 00:00	331
XXXX	201815	22.10.2018 00:00	411
XXXX	201815	23.10.2018 00:00
XXXX	201815	24.10.2018 00:00
XXXX	201815	25.10.2018 00:00
XXXX	201815	26.10.2018 00:00	579
XXXX	201815	27.10.2018 00:00

I'm not a beginner in DAX & PowerBI, but I'm confused and frustrated a bit. I was also tryning to change my model relationships ("Both"/"Single" filtering)...

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Please download the demo from the attachment. The measure works. The relationship should be "single filter".

Running total sales measure test =
CALCULATE (
    SUM ( 'Sales & Prizes'[Running Total] ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

Best Regards,
Dale