cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bapt69
Helper IV
Helper IV

cumulative total and different year

Hi everyone,

I would like to calculate a ratio (cumulative month by month) : nb customer who have paid/nb customer who have to pay, for each year

I got my list of customers with the paiement date (or empty if not paid) :

customer (Num_Tiers) | year (Année) | amount awaited (Objectif) | YYYY_MM paiement (Période_Télédéclaration) | Paiement date

 

I would like a graph, month by month (axis X), with the ratio (axis Y) nb customer who have paid/nb customer who have to pay. One line for each year (2019 and 2020).

 

I tried this : 

tx_télédéclaration2019 = DIVIDE(calculate(DISTINCTCOUNT(BI_OBJECTIFS_AP[Num_Tiers]);left(BI_OBJECTIFS_AP[Période_Télédéclaration2019];4)="2019");calculate(DISTINCTCOUNT(BI_OBJECTIFS_AP[Num_Tiers]);BI_OBJECTIFS_AP[Objectif]>0;BI_OBJECTIFS_AP[Année]=2019;all(BI_date)))
First, it's not a cumulate total,
then, I got only 2019.
 
Thanx for all,
 
1 ACCEPTED SOLUTION

Hi @bapt69 

It seems your formula is not correct.

DATESYTD resets every year. If the goal is to sum values over more than one year, then DATESYTD is no longer useful.

Please learn how to create formula for cumulative total from links below:

https://www.daxpatterns.com/cumulative-total/

https://www.sqlbi.com/articles/computing-running-totals-in-dax/

If you have any problems, feel free to let me know.

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

7 REPLIES 7
v-juanli-msft
Community Support
Community Support

Hi @bapt69 

Would you like the result as below:

Capture15.JPG

 

Best Regards

Maggie

amitchandak
Super User
Super User

You can use datesytd and totalytd with date date to get these.

Example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

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/

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Sorry,

There is 2 paiement periods.

 

customer | year | budget | paiement date 2019 | paiement date 2020

CRM-002| 2019 | 4520€   |   2019_02                 |           2020_01

CRM-002| 2020 | 4000€  |    2019_02                 |           2020_01

CRM-003...

...

 

First of all, do I need to fill once time the paiement date like this :

customer | year | budget | paiement date 2019 | paiement date 2020

CRM-002| 2019 | 4520€   |   2019_02                 |             

CRM-002| 2020 | 4000€  |                                   |           2020_01

CRM-003...

 

Yes I have a DATE Table and the relation is on YYYY_MM column (cause my Date Table don't hab every calendar date - the unique value is the YYYY_MM and the first day of every month)

YTD will give cummaltive. You should create complete calendar. 

Now if you have only YYYY_MM in you table convert to date like this

 

date =date(left(table[date],4),right(table[date],2),1)

 

using time intelligence will be a better idea.

 

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/

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

No way! Am I dumb?! 😄

 

My measure : 

calculate(DISTINCTCOUNT(BI_OBJECTIFS_AP[accountnumber]);DATESYTD(BI_OBJECTIFS_AP[Date_périod]);filter2;filter3...)
with date_period=first day of the month for each paiement (not the actual paiement date)
 
My Date Table and the other table with a relation Date.date->Date_period
 
And I still got a number per month, not a cumulative total...
I tried TOTALYTD too, same issue...
 

Hi @bapt69 

It seems your formula is not correct.

DATESYTD resets every year. If the goal is to sum values over more than one year, then DATESYTD is no longer useful.

Please learn how to create formula for cumulative total from links below:

https://www.daxpatterns.com/cumulative-total/

https://www.sqlbi.com/articles/computing-running-totals-in-dax/

If you have any problems, feel free to let me know.

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Please make sure from the date table you are using the date that is joined with fact or marked as the date while making it the "The date"table.

 

I had made mistake in past using another date column from date table, which was also a date.

Refer to this file. It has quite a few formulae. Please check the one you need. Keep on removing the pages you don't need

https://www.dropbox.com/s/bu47m5ek7sr9v8t/sales_analytics_v2.pbix?dl=0

 

For date, calendar step refer: Decoding Direct Query - Time Intelligence.

In import, you have the option in data tab

Appreciate your Kudos.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.