cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Molcy
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.

 

2020-01-31 13_41_21-Power BI Atölye Çalışması_9.bölüm - Power BI Desktop.png2020-01-31 13_35_37-Power BI Atölye Çalışması_9.bölüm - Power BI Desktop.png2020-01-31 11_24_11-Power BI Atölye Çalışması_9.bölüm - Power BI Desktop.png

 

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.

 


Please see below table,

 


I hope i could explaing my situation,

mant thanks for your replys

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....2020-01-31 11_24_11-Power BI Atölye Çalışması_9.bölüm - Power BI Desktop.png

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
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] ) )
    )
)

 

9.jpg

 

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.

View solution in original post

8 REPLIES 8
v-lid-msft
Community Support
Community Support

Hi @Molcy ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


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.
v-lid-msft
Community Support
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] ) )
    )
)

 

9.jpg

 

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.

You are the best 🙂 

amitchandak
Super User
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

Connect on Linkedin



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 !!

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

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://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
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 !!

Thanks for quick reply. 

 

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

 

Refer : https://community.powerbi.com/t5/Desktop/calculate-category-total-in-table-visualization/td-p/519340

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 !!

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors