Sales MTD on a daily level

[joshua1990]

Hey guys,

I have a matrix that shows me all dates for the current fiscal month with the corresponding sales for each day.

Fiscal Week	Date	Sales	Sales MTD
14	05.04.2021
14	06.04.2021	2.111.765	2.111.765
14	07.04.2021	2.050.900	4.162.665
14	08.04.2021	1.767.297	5.929.962
14	09.04.2021	315.773	6.245.735
14	10.04.2021
14	11.04.2021

As you can see, I have also a Sales MTD column that is based on this measure:

Sales MTD = 
VAR RunningTotal =
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Fiscal Year] = VALUES ( 'Calendar'[Fiscal Year] )
                && 'Calendar'[Fiscal Month Number] = VALUES ( 'Calendar'[Fiscal Month Number] )
                && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        )
    )
RETURN
    IF ( [Sales], RunningTotal )

Unfortunately for weekends, when no sales were executed, I get no value in the corresponding row in Sales MTD.

How can I adjust the measure to have always a value in Sales MTD for the past and for today?

[daxer-almighty]

@joshua1990 

Your problem is easy to remove. Just don't use IF at the end of your formula. Return RunningTotal as it is. If you want remove the amounts in the future, then you have to change your formula. Here's what you have to do. You have to find out the very latest day D from the fact table that has any sales. Then, if your selected period of time (be it a day or a month) does not have any days in common with the future, meaning days after D, then you should return BLANK. For all other days, you should return the running total. Bear in mind as well that your formula should only work on the day and month level, so it's crucial that in your measure you check if this is true and only then calculate the figure. That's because it makes no sense to return MTD for periods of time that are longer than months.

[Jihwan_Kim]

Hi, @joshua1990 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

All measures are in the sample pbix file.

https://www.dropbox.com/s/gtk9w7u0j3ap5s4/joshua1990.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[joshua1990]

Thanks, but this will not work since I have a fiscal calendar with a specific structure. These Time Intelligence DAX Functions will not work.