Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'm looking to include a column in a table which includes sales YTD, in a monthly view.
E.g. If I filter by August, it includes sales YTD for Jan-Aug.
This is the current formula:
Total Sales YTD =
VAR RetVal =
CALCULATE(
SUM('Revenue'[Sales Booked Amt - at Plan Rt]),
ALL('DATE_DIM'),
VALUES(DATE_DIM[YR_NBR])
)
RETURN
IF(
MAX('DATE_DIM'[MNTH_NBR]) <= SELECTEDVALUE(DATE_DIM[MNTH_NBR]),
RetVal)
The issue I have is that to include a YTD calculation, I need to exclude the month filter to have Jan-Aug.
However, this then includes September and October data. Instead, I need the range to max at selected month value
Does anyone know how I can recode this to have it exclude anything after SELECTED VALUE of the month filter?
Unfortunately, I can't create another table as I need drill down capabilities.
Thanks!
Solved! Go to Solution.
Hi @JLarkin
Please try
Total Sales YTD =
CALCULATE (
SUM ( 'Revenue'[Sales Booked Amt - at Plan Rt] ),
FILTER (
ALL ( 'DATE_DIM' ),
'DATE_DIM'[Month Number] <= MAX ( 'DATE_DIM'[Month Number] )
),
VALUES ( DATE_DIM[YR_NBR] )
)
Hi @JLarkin
please try
Sales YTD PY =
CALCULATE(
SUM('Revenue'[Sales Booked Amt - at Plan Rt]),
FILTER(
ALL('DATE_DIM'),
'DATE_DIM'[MNTH_NBR] <= MAX('DATE_DIM'[MNTH_NBR])
&& 'DATE_DIM'[YR_NBR] = (MAX('DATE_DIM'[YR_NBR])-1)
)
)
Hi @JLarkin
Please try
Total Sales YTD =
CALCULATE (
SUM ( 'Revenue'[Sales Booked Amt - at Plan Rt] ),
FILTER (
ALL ( 'DATE_DIM' ),
'DATE_DIM'[Month Number] <= MAX ( 'DATE_DIM'[Month Number] )
),
VALUES ( DATE_DIM[YR_NBR] )
)
Hi @tamerj1, and others,
I was wondering if you could perhaps assist me with a new and similar measure.
Rather than just a Total Sales YTD, I'm looking to have a Sales YTD of last year - where we were this time last year.
Here is the code I have
Sales YTD PY =
CALCULATE(
SUM('Revenue'[Sales Booked Amt - at Plan Rt]),
FILTER(
ALL('DATE_DIM'),
'DATE_DIM'[MNTH_NBR] <= MAX('DATE_DIM'[MNTH_NBR])
&& 'DATE_DIM'[YR_NBR] = (MAX('DATE_DIM'[YR_NBR])-1)
),
VALUES(DATE_DIM[YR_NBR])
)
This works to an extent, in the sense that if I select 2021 and 2022 in the filters, it will show 2021 data.
But this isn't practical, as it changes the other figures. I would need it to show 2021's figures when 2022 is selected only.
Thank you in advance!
Hi @JLarkin
please try
Sales YTD PY =
CALCULATE(
SUM('Revenue'[Sales Booked Amt - at Plan Rt]),
FILTER(
ALL('DATE_DIM'),
'DATE_DIM'[MNTH_NBR] <= MAX('DATE_DIM'[MNTH_NBR])
&& 'DATE_DIM'[YR_NBR] = (MAX('DATE_DIM'[YR_NBR])-1)
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
15 | |
10 |
User | Count |
---|---|
57 | |
50 | |
44 | |
21 | |
19 |