İncreasing Day By Day

[erhan_79]

Hi there ;

i need  your  kind support to calculate below issue on my report  ;

İ have a table which one i added with below picture named " Table A " , in this table there is date , sales group and amount.

everyday new amounts are adding to this table  for each days , just for this example i mentioned and focused for three days (01.06.2020 , 02.06.2020 , 03.06.2020  )

i would like to create another table which one as below named "Table B" , in this table i need to add amount for each day to previous day.

İ tried to explain near "Table B "  , cell by cell what i need . I hope it is clear . thanks in advance for your supports 

[amitchandak · Accepted solution]

@erhan_79 ,

Please try a measure like this with date table

Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(date,date[date] <=maxx(date,date[date])))

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[amitchandak]

@erhan_79 , Try a new column in same table

sumx(filter(table,[sales group] = earlier([sales group]) && [Date] <= earlier([Date])),[Amount])

[erhan_79]

@amitchandak 

i noticed that while giving info to you i made a mistake , my revised tables as below , just i want to sum days as below could you pls help ?

[amitchandak · Accepted solution]

@erhan_79 ,

Please try a measure like this with date table

Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(date,date[date] <=maxx(date,date[date])))

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[erhan_79]

Dear @amitchandak  ;

thank you very much for your reply ;

i created measure as you mentioned also i created an date table too , but something i not working, for your checking i am uploading below  my test pbix file's link :

https://drive.google.com/file/d/1HTjL2ldKWxp2xSAJI3lVAgwCeFltKDac/view?usp=sharing

 i am sure when you will see my file you will quickly find where i am wrong.

if you will check my previous message i need to sum everyday , first day a value , secon day will be first day and second day's total , third day will be first and second and third day's total and etc.

but on my test pbix as you see system just calculating for each days total an put the graphic.

Could you pls check it for me 

[harshnathani · Accepted solution]

Hi @erhan_79 ,

Use this measure

Calculate sum1 = CALCULATE(SUM('Table A'[Amount]),FILTER(ALL('Table A'),'Table A'[Date ] <= MAX('Table A'[Date ])))

Pull Date from Table A an Sum1 from Table A into the visual

Also, incase you want to pull Date from DimDate Table

use the below measure

Calculate sum = CALCULATE(SUM('Table A'[Amount]),FILTER(ALL(Dimdate),Dimdate[Date] <= MAX(Dimdate[Date])))

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[erhan_79]

Dear @harshnathani 

you created for  me measure , i would like to create table same one , could you pls give table formula too?

[harshnathani]

Hi @erhan_79 ,

Create a Table

Table =

SUMMARIZE('Table A','Table A'[Date ],"Total Amount", SUM('Table A'[Amount]))

Then create a Calculated Column

Running total =

var a = MAX('Table'[Date ])

RETURN

CALCULATE(SUM('Table'[Total Amount]), FILTER('Table','Table'[Date ] <= EARLIER('Table'[Date ])))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)