Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bombom
Helper I
Helper I

Recurring monthly total every day for a month

Hello! I want to make a calculated measure in DAX so it calculates total sales per month and repeat that number for every day of the month on the graph. I am allowed to achieve calculate staked sales from day by day, but can't understand how to calculate total sales for month and repeat that number every day (yellow line). The formulas for staked sales 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]))

 

изображение_2022-11-02_211141529.png

1 ACCEPTED SOLUTION

Hi, @bombom 

 

I understand your question, you can check out the following methods.
Measure:

Cumulative sum = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),[Date]<=SELECTEDVALUE('Table'[Date])))
Goal = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),MONTH([Date])=3&&YEAR([Date])=2017))

vzhangti_0-1667890066041.png

Result:

vzhangti_1-1667890099731.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

View solution in original post

4 REPLIES 4
v-zhangti
Community Support
Community Support

Hi, @bombom 

 

Can you simplify your data by providing some sample data tests? Sensitive information can be removed in advance. What kind of expected results do you expect? 

 

Best Regards,

Community Support Team _Charlotte

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

Hello, @v-zhangti 

 

Thank you for reaching my question.

Here is the link to dataset https://drive.google.com/drive/folders/1j5HZIYGdmNUxqZm-XcIZ7VNKd32iuWAT

The goal is to sum total revenue of the month and duplicate this number across each day of the month, so it will create a one-way flat line chart.

For example, on the screenshot is a chart from dataset, March 2017, that calculates revenue inside one month day by day accumulatively. On the last day the revenue equals 1 448 594,08. In other words - it is a total revenue of the month. The goal is to take this total revenue, 1 448 594,08, and duplicate it for everyday of the month. 

изображение_2022-11-03_121844441.png

Hi, @bombom 

 

I understand your question, you can check out the following methods.
Measure:

Cumulative sum = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),[Date]<=SELECTEDVALUE('Table'[Date])))
Goal = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),MONTH([Date])=3&&YEAR([Date])=2017))

vzhangti_0-1667890066041.png

Result:

vzhangti_1-1667890099731.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

Hello @v-zhangti 

 

Thank you so much, looks good!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors