Forum Discussion
Post Prodigyincreasing day by day table
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
Hi erhan_79 ,
Would you please refer to the dax below based on the pbix file you provided:
New Table = SUMMARIZE ( 'Table A', 'Dimdate'[Date], 'Table A'[Sales Group], "Amount", CALCULATE ( SUM ( 'Table A'[Amount] ), FILTER ( ALLEXCEPT ( 'Table A', 'Table A'[Sales Group] ), 'Table A'[Report Date] <= MAX ( 'Table A'[Report Date] ) && 'Table A'[Billing Type] <> "F2" ) ) )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
9 Replies
- amitchandak
Super User
erhan_79 , Create a date table join with date and try one of the three options
summarize(Table, Date[Date],table[sales Group],"Amount",CALCULATE(SUM(Table[Amount]),filter(date,date[date] <=maxx(date,date[date])))) summarize(Date, Date[Date],table[sales Group],"Amount",CALCULATE(SUM(Table[Amount]),filter(date,date[date] <=maxx(date,date[date])))) summarize(Date, Date[Date],table[sales Group],"Amount",CALCULATE(SUM(Table[Amount]),filter(all(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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
erhan_79dear amitchandak ;
i tried your first formula , i created also a date table too , somethng 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
This table i want :
but system is calculating as below :
Could you pls help
- amitchandak
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])))
