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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

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])

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

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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