Repeat sum of Month's total on each date

[Molcy]

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.

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

[v-lid-msft]

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,

[v-lid-msft · Accepted solution]

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,

[raomohsin54]

You are the best 🙂 

[amitchandak]

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

[Molcy]

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

[amitchandak]

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/

[Molcy]

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

[amitchandak]

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

Or you can use quick measures