Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am having an issue calculating the YTD volume from a dataset.
I have a supply end date date slicer which is a between e.g user selects 01/02/2021 - 28/02/2021
I need to calculate the YTD Volume from the chosen date period.
Below is an example of the YTD data if 01/02/2021 - 28/02/2021 is selected.
TIA
Hi @yaman123
Create Date_slicer table as:
Date Slicer =
CALENDAR(MIN('Table'[SUPPLY_END_DATE]),MAX('Table'[SUPPLY_END_DATE]))
Try measure as:
Measure =
var min_date=
EOMONTH(MINX('Date Slicer',SELECTEDVALUE('Date Slicer'[Date],MAX('Date Slicer'[Date]))),-11)
var max_date=
MINX('Date Slicer',SELECTEDVALUE('Date Slicer'[Date],MAX('Date Slicer'[Date])))
return
CALCULATE(
MAX('Table'[COLLECTION_LITRES]),
FILTER(
('Table'),
'Table'[SUPPLY_END_DATE]<=max_date && 'Table'[SUPPLY_END_DATE]>=min_date
)
)
Here is the output:
The pbix file is attached.
Best Regards,
Link
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@yaman123 , YTD will be from the start of the year
This seems like sum(Table[collection_liter])
YTD with help from date table
calculation(sum(Table[collection_liter]) ,datesytd('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 :radacad sqlbi My Video Series Appreciate your Kudos.
Hi,
I need the YTD to be -11 months from the chosen dates. How can this be done?
@yaman123 , Try like
Rolling 11 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-11,MONTH))
or
measure =
var _max = maxx(allselected('Date1'), 'Date1'[Date])
var _min = eomonth(minx(all('Date'), 'Date'[Date]),-11)
return
calculate(sum(Sales[Sales Amount]), filter(all('Date'),'Date'[Date] <= _max && 'Date'[Date] >=_min))
Hi,
That also doesnt work. My slicer is is a between selection, so two dates are selected. I am not getting the correct figures from the code you have provided.