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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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/

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.