The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to Solution.
@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
@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
User | Count |
---|---|
17 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
9 | |
8 |