cancel
Showing results for
Did you mean:
New Member

## Repeat sum of Month's total on each date

Hi All,

I found the solution by creating a new table and group the monthnyear column and then using the relationship I evaluate CALCULATE (sum[SALES[ordervalue])) in a new calculated column. And then I bring in this new created value by RELATED to my sales data. Then by using simple formula called MAX, i have done it.

However,
I'm struggling to find a solution for a calculation that i am trying to do in a measure.

I am trying to repeat the sum of each months sales value on each date in a table.

I have tried cumulative totals using MTD, ALLSELECTED, TOTALMTD etc but I could not get the solution.

I hope i could explaing my situation,

Cheers!

Dates

01/01/2020 - 100 - 800

02/01/2020 - 200 - 800

03/01/2020 - 300 - 800

04/01/2020 - 100 - 800

.

.

.

31/01/2020 - 100 - 800

01/02/2020 - 100 - 350

02/02/2020 - 150 - 350

.

.

.

29/02/2020 - 100 - 350

.

.

.

31/12/2020....

1 ACCEPTED SOLUTION
Community Support

Hi @Molcy ,

We can create a measure to meet your requirement:

MonthTotal =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Date].[MonthNo] = MONTH ( SELECTEDVALUE ( 'Table'[Date] ) )
&& 'Table'[Date].[Year] = YEAR ( SELECTEDVALUE ( 'Table'[Date] ) )
)
)

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we  have shared.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Community Support

Hi @Molcy ,

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support

Hi @Molcy ,

We can create a measure to meet your requirement:

MonthTotal =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Date].[MonthNo] = MONTH ( SELECTEDVALUE ( 'Table'[Date] ) )
&& 'Table'[Date].[Year] = YEAR ( SELECTEDVALUE ( 'Table'[Date] ) )
)
)

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we  have shared.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

You are the best 🙂

Super User

create the following column

stmonth = STARTOFMONTH(table[date])

This month sales = sumx(filter(table,table[stmonth]=earlier(table[stmonth])),table[sales])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
New Member

Hi

I created a column in my sales table using below;

Stmonth = STARTOFMONTH(Siparis_Data[SiparisTarihi])

And then i created the following measure:

This Month Sales = SUMX(FILTER(Siparis_Data;Siparis_Data[stmonth]=EARLIER(Siparis_Data[stmonth]));Siparis_Data[ToplamSatis])

However, it gives a warning, saying earlier refers to an earlier row context which does not exist

what could be the problem?

Regards
many thanks
Super User

The second one is also a column. If you need a measure then try allexcept. Also prefer a date dim in that case

This Month Sales = calculate( SUM(Siparis_Data[ToplamSatis]),allexcept(Siparis_Data[stmonth]))
This Month Sales = calculate( SUM(Siparis_Data[ToplamSatis]),allexcept(date[stmonth]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
New Member

I have a calander table which work just fine.

This Month Sales = calculate( SUM(Siparis_Data[ToplamSatis]),allexcept(Siparis_Data[stmonth])) or;

This Month Sales = calculate( SUM(Siparis_Data[ToplamSatis]),allexcept(date[stmonth])) works both fine.

This formula returns total sales for each row.  I mean Grand Total sales.

However this is not I am trying to do. What i am trying to do is;

If January total sales is 150.000 for instance, then I want each dates in January repeat 150.000,

If February total sales is 200.000 for instance, then I want each dates in February repeat 200.000,

so on and so forth....

Thanks

Super User

Or you can use quick measures

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Announcements