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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
erhan_79
Post Prodigy
Post Prodigy

İncreasing Day By Day

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 

 

 

Capture-6.JPG

2 ACCEPTED SOLUTIONS

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

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])))
 
 
1.jpg
 
Regards,
Harsh Nathani

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

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@erhan_79 , Try a new column in same table

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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 ?

 

Capture-7.JPG

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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 

 

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])))
 
 
1.jpg
 
Regards,
Harsh Nathani

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

Dear @harshnathani 

 

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

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 ])))
 
 
1.jpg
 
 

Regards,
Harsh Nathani

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.