Dear DNA team,
I would like to request to help my issue below:
My current YTD and QTD is simple as usual, b
New logic for YTD and QTD.pbix (319.8 KB)
rawdata.xlsx (1.8 MB)
ut user wants to change the logic as below picture, is it possible and could you help me to write the DAX to implement according to new logic?
The baseline current value is Total Amount measures which already exist. Please refer to the attached pbix file for your reference. Please see the detail logic in the ‘logic’ tab of exel.
Thanks in advance and appreciate if you would reply me as soon as possible.
Current formula for April-2023: From Jan to April2023
New formula for April-2023: Avg of Dec2022 and April2023 (Please help)
Thanks in advance
Myat
Dear Myat, @anonymous124 ,
Welcome to the forum !!
A possible solution is:
- Adding additional calc. columns with revised starting dates and periods for YTD and QTD to the date table
- Working with the standard Datesbetween Dax function, based upon those columns
See attached PBIX:
New logic for YTD and QTD v2.pbix (127.3 KB)
Workout explained: (dummy data used)
Ad 1) Four additional columns in the DAX-generated time table, as the basis for the Datesbetween funtions:
PS: not tested on large data sets, performance-wise it might be better to export this lookup date-table to Excel and import it again without the DAX-formulas
Ad 2) The requested revised Avg QTD- and YTD averages can be calculated with the use of Datesbetween:
Revised Average YTD =
Var RevYTD88 = CALCULATE(SUM(DummySales[Amount]),DATESBETWEEN( 'Calendar'[Date],max('Calendar'[Rev_YTDStartDate]),max('Calendar'[Date])))
Return DIVIDE( RevYTD88, MAX('Calendar'[Rev_YTDMonths]),0)
PS: The max-function is needed in the measure, as there are multiple lines per period in the date table, it selects the max with the selected context (monthly period)
The outcome can be verified in 2 pages, “Revised Average YTD” is presented below:
Hopefully this helps you further, a nice weekend,
kind regards, Jan vd Wind
1 Like
Dear Wind,
Very thank you for your explanation, YTD is exactly what I requested but QTD is not. I may need to use some trick based on your guide.
Let me ask you if I still facing issues or could not solve myself. Thank you very much.
Kind Regards
Myat
@anonymous124
Indeed QTD Starting Date needed correction to:
Kind regards Jan vd Wind
