Cumulative Totals - To Specific Date Actuals vs Targets

DAX DAX Calculations
1

Hi DNA team,

I’m looking for a help with my Budgets vs Targets Model. SCF FIN1.pbix (324.1 KB)

I would like to compare Sales vs Targets Cumulatively on one line graph.

The model houses 4 tables:

  • SCF 3 - Sales and SCF 3 Targets ( These Sales run from May 2015 - 2019 )

  • SCF 4 - Sales and SCF 4 Targets ( These Sales run from May 2019 - 2023)

Melissa was kind enough to help with the creation of a calculated column a few months back that allocated the month number to sales dates in the 4 year cycle described above. Please to the thread

Therefore, Ideally i would like to should all 4 measures in the one graphs by FW Month
Ive have managed to show all required visuals on a graph by the date as you will see. However, that visuals on the Targets lines looked stepped is not the greatest. I believe it might be a relationship problem in the model, coupled with some additonal dax to make it work.

Any help would be great.

Kind regards

Adam

2

Hello @adam. We have also have threads with some recommendations from other Power BI users which could help. Below are the links where you can view them. Hope this helps.

3

Hi @adam

I have come up with below solution, please check if it is helpful for you.

  1. Made changes to your Data Model. My advice is to always make 1 to Many Relationship b/w Date Table and Fact table to get best results.

  2. Modified Cumulative Sales Column as below. This will only return Cumulative Sales for dates when there is any value in SCF 3.

    SCF 3 Cumulative Sales =
    IF (
    ISBLANK ( [SCF 3 Total Sales] ),
    BLANK (),
    CALCULATE (
    [SCF 3 Total Sales],
    FILTER ( ALLSELECTED ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
    ))

  3. To Calculate Target Sales, used TREATAS function. This function creates Virtual relationship and is really helpful in these situations.

    SCF 3 Target Sales Final =
    //var DaysCount = CALCULATE(countrows(Dates),ALL(dates),values(Dates[Month & Year]))
    CALCULATE (
    [SCF 3 Target Sales],
    TREATAS ( VALUES ( Dates[Month & Year] ), ‘Targets 3’[Month and Year] )
    )

  4. Similar to Sales, I am only Calculating Cumulative Targets for Dates where we have values in SCF 3.

    SCF 3 Cumulative Targets =

    IF (
    ISBLANK ( [SCF 3 Total Sales] ),
    BLANK (),
    CALCULATE (
    [SCF 3 Target Sales Final],
    FILTER ( ALLSELECTED ( Dates[Date] ), dates[date] <= MAX ( Dates[Date] ) )))

Final output is as below. The same can be replicated for SCF 4.

image

Let me know if it is helpful or any other help is required. Attached the Solution. SCF FIN1.pbix (313.9 KB)

Thanks
Ankit Jain

2 Likes
4

Thank you Ankit Legend!

I will work through this on Monday and replicate for SCF 4. Hopefully ill be able to layer SCF3 and SCF4 into one line chart using FW Month.

Thanks again.

Adam

5

Hi @adam, did the response provided by @Ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

6

Hi @adam, we’ve noticed that no response has been received from you since the 7th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!