Cumulative Total Of A Measure Between Selected Dates

[Ivolution93]

Hello

I have a measure which is the following

Daily Target =

DIVIDE(

    AVERAGE('Combined Invoices'[BudgetDailySalesTarget]),

    AVERAGE('Combined Invoices'[WorkingDays])

)

This Gives me a daily sales target.

Does anybody know how to then use this to get a second target which is the cumalative target.

for example if the Daily target was worked out at 25,000 then i would want the results to show as follows

first date in the dates selected 25,000.00, second date 50,000.00 then if there is 10 dates selected the last date to show a cumulative target to be 250,000.

25000

50000

75000

100000

125000

150000

175000

200000

225000

250000

i have tried using a calculate however when i put calculate it then doesnt allow me to use Daily Target. Quite new to Dax and not sure why this might be.

[v-alq-msft · Accepted solution]

Hi, @Ivolution93 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below:

Result = 
var tab =
SUMMARIZE(
         ALL('Table'),
         'Table'[Date],
         "Result",
         DIVIDE(
             SUM('Table'[BudgetDailySalesTarget]),
             SUM('Table'[WorkingDays])
         )
)
return
SUMX(
    FILTER(
        tab,
        [Date]<=MAX('Table'[Date])
    ),
    [Result]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Ivolution93 , usuayllt you can get Cumulative like

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(Sales),Sales[Sales Date] <=max(Sales[Sales Date])))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Ivolution93]

Hi @amitchandak 

I did actually find a calculation identical to this on a few other threads and videos online, however i wasnt able to add the daily target in instead of the sales amount, i amsuming this is because it is a measure itself.

My data is connected to a SQL database, how would i share any data with you, sorry if this is a silly question i am relativly new to alot of things in power bi

[amitchandak]

@Ivolution93 , you can paste some same data here on the thread or upload sample data on onedrive or dropbox and share the link.

share expected output too

[Ivolution93]

Hi

the link for 2 months of data is https://drive.google.com/file/d/1ESLqhqK5AkGV0iQLScV3pDdlezT3lDLF/view?usp=sharing 

I have included date, monthly target, working days in the month and then daily target.

i am after a cumulative daily target between the any 2 selected dates from the tax date field.

the target is by day so on power bi when you put this on a visual the correct target is average on that day rather than sum.