DAX Measure calculated total of month repeats everyday

[bombom]

Hello! I want to create a DAX measure so it calculates the total sales of the month a this number will appear on the graph for everyday of the calculated month. Desirable result is filled with yeloow. Any help please?

The formulas of staked metrics look like this:

Revenue_staked = CALCULATE(SUMX(AW_Sales, AW_Sales[OrderQuantity] * RELATED(AW_Product_Lookup[ProductPrice])),DATESMTD(AW_Calendar_Lookup[Date]))

Revenue_staked_prev_month = CALCULATE([Revenue_staked], DATEADD(AW_Calendar_Lookup[Date],-1,MONTH))

Profit_staked = CALCULATE(SUMX(AW_Sales, AW_Sales[OrderQuantity] * RELATED(AW_Product_Lookup[ProductPrice])),DATESMTD(AW_Calendar_Lookup[Date])) - CALCULATE(SUMX(AW_Sales, AW_Sales[OrderQuantity] * RELATED(AW_Product_Lookup[ProductCost])),DATESMTD(AW_Calendar_Lookup[Date]))

[vojtechsima]

Hi, @bombom ,
So you can play with it like this:

If you select your visual, you can click this button as shown, and add constant line by Field.

The field would be the sum in month, for example something like this (Day = Day in Month):

Sales MTD = TOTALMTD([Sales], 'Calendar'[Date], ALLSELECTED('Calendar'[Day]))

Then it could look like this:

[bombom]

Thank you @vojtechsima for reaching my question!

I've tried to create a constant line, but, unfortunatelly, I couldn't find the field called Data label. Looks like it's due to I'm using old version of PBI Dec 2020, cause I'm using Windows 7. And I've tried to use a formula alone, with out a constant line, and so this formula calculated cumulative total from the first till the last day, like all another lines.

Maybe there any option, like in SQL's window functions, to make calculation inside DAX to sum sales over  partition by month and as a result it will duplicate the same total amount for each day of the calculated month?

[vojtechsima · Accepted solution]

@bombom 
Hi, I strongly recommend using the latest version, howver if that's not possible try this trick:

Sales MTD Fix = 
CALCULATE(
    [Sales MTD],
    TREATAS(ENDOFMONTH('Calendar'[Date]),'Calendar'[Date]))

[bombom]

@vojtechsima 

Everything worked! Thank you so much sir! We did it

[vojtechsima]

Happy to help, my man. @bombom