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

Need help ith formula

Hi there ;

 

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

 

i have a table which one's name is Table A as below at left , i would like to calculate day by day increasing of amount based on date and based on Sales Group .Here some users adviced me to use to try "running total"  with quick measure but it s not available for my scenarios  , i need a new table as below at right , could you pls give the formula for new table pls 

 

thanks in advance 

 

Capture-18.JPG

 

 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@erhan_79 

 

Please try below measure to create a new table.

Table 3 = 
SUMMARIZE('Table (2)','Table (2)'[reportdate],'Table (2)'[salesgroup],"amount",CALCULATE(sum('Table (2)'[Amount]),FILTER(ALLEXCEPT('Table (2)','Table (2)'[salesgroup]),'Table (2)'[reportdate]<=EARLIER('Table (2)'[reportdate]))))

1.PNG

hope this is helpful.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
ryan_mayu
Super User
Super User

@erhan_79 

 

Please try below measure to create a new table.

Table 3 = 
SUMMARIZE('Table (2)','Table (2)'[reportdate],'Table (2)'[salesgroup],"amount",CALCULATE(sum('Table (2)'[Amount]),FILTER(ALLEXCEPT('Table (2)','Table (2)'[salesgroup]),'Table (2)'[reportdate]<=EARLIER('Table (2)'[reportdate]))))

1.PNG

hope this is helpful.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

@erhan_79 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Hi  @amitchandak  ;

 

Infact today i mentioned this issue another ticket , i thought that you were busy , so i reopen again topic 

 

summarize(Table, Date[Date],table[sales Group],"Amount",CALCULATE(SUM(Table[Amount]),filter(date,date[date] <=maxx(date,date[date]))))

 

 i tried your below formula  that you gave me today  , i created also a date table too , but something wrong and i can not solve what i tried .

 

i wanted that everyday system will increase value to previous day based on sales group , but formula calculate just totals for each days 

 

for your easy checking i am sharing my test file 

 

https://drive.google.com/file/d/1Ug3gP3GtYAdgsQui-Fbbp90MzFhlkVNS/view?usp=sharing

 

 

İ have table A ,  below table named "table new " that  i want to create new   , if you should  focus amounts  below  new table  right , first day (15 june ) are 15 June's amounts , but for next days system adding previous day's amount and shows like that , and next day again add all previous day's amounts based on Sales Group  :

 

Capture20.JPG

 

i created new table with my test pbix as below , it is not same as you see my above "new table " namedCapture21.JPG

 

i hope it is clear now : 

 

 

 

 

 

@erhan_79 ,

I create a measure and used it in display table with date from date table and sales group from table and got the required visual table

 

Measure = CALCULATE(SUM('Table A'[Amount]),FILTER(all(Dimdate),Dimdate[Date]<=MAXX('Table A','Table A'[Report Date])))

 

 

Helpful resources

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