topic Re: Show cumul only in last month in graph in DAX Commands and Tips

Show cumul only in last month in graph

Juju123 — Tue, 17 Sep 2024 13:33:08 GMT

Good morning,

I have the following problem that I cannot resolve.
I have this type of chart.

I want to display the cumulative value only for the last month.

For example, if I select the company B130, I want all the values and display the cumulative total with a point for the month of September.

Likewise, if I select company B220, I want to display the cumulative total with a point for the month of August.

Here is my DAX formula:

Cumul = IF(MONTH(SELECTEDVALUE('Entity (Feuil1)'[Date]))=MONTH(TODAY()) && YEAR(SELECTEDVALUE('Entity (Feuil1)'[Date]))=YEAR(TODAY()), Calculate ( sum('Entity (Feuil1)'[Order]), FILTER( ALLSELECTED('Entity (Feuil1)'[Date]), ISONORAFTER('Entity (Feuil1)'[Date], MAX('Entity (Feuil1)'[Date]), DESC) ) ),BLANK())

My data it this :

Entity	Date	Order
B130	30/04/2020	1
B130	12/05/2023	1
B130	22/06/2023	3
B130	12/12/2023	2
B130	01/08/2024	2
B130	14/09/2024	1
B220	01/02/2023	1
B220	04/07/2023	1
B220	05/03/2024	1
B220	11/08/2024	2
B220	02/06/2024	1

Thank you in advance for your help

Re: Show cumul only in last month in graph

BeaBF — Tue, 17 Sep 2024 13:44:14 GMT

Juju123 Hi! Try with:

Cumul =
VAR LastMonthDate = CALCULATE(
MAX('Entity (Feuil1)'[Date]),
ALLEXCEPT('Entity (Feuil1)', 'Entity (Feuil1)'[Entity])
)
VAR LastMonth = MONTH(LastMonthDate)
VAR LastYear = YEAR(LastMonthDate)

RETURN
IF (
MONTH(SELECTEDVALUE('Entity (Feuil1)'[Date])) = LastMonth &&
YEAR(SELECTEDVALUE('Entity (Feuil1)'[Date])) = LastYear,
CALCULATE(
SUM('Entity (Feuil1)'[Order]),
FILTER(
ALLSELECTED('Entity (Feuil1)'[Date]),
'Entity (Feuil1)'[Date] <= LastMonthDate
)
),
BLANK()
)

BBF

Re: Show cumul only in last month in graph

Anonymous — Wed, 18 Sep 2024 09:35:06 GMT

Hi Juju123 ,

Did BeaBF reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best regards,

Adamk Kong

Re: Show cumul only in last month in graph

Juju123 — Thu, 19 Sep 2024 08:46:13 GMT

Hi BeaBF , thanks you so much it's work fine !