Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there,
I am trying to build a visual for WTD, MTD & YTD with one slicer for date and second slicer for WTD,MTD & YTD.
so when a user selects a specific date range untill today , they can get the values for WTD, MTD or YTD as per selection
Below is the bar chart visual which will give me the value for revenue as per the above selection , I want to display in the visual the x-axis dynamically , so when the user selects "YTD" they get view monthly. "WTD" to show the current week data in daily . "MTD" to show the current month.
But the view needs to change as per the selection in the main date slicer.
I have used calculation groups to get the WTD, MTD & YTD .
WTD = VAR CurrentDate = LASTDATE('Date'[Full Date]) VAR DayNumberOfWeek = WEEKDAY(LASTDATE('Date'[Full Date]),2) RETURN CALCULATE( SELECTEDMEASURE(), DATESBETWEEN( 'Date'[Full Date], DATEADD( CurrentDate, -1*DayNumberOfWeek, DAY), CurrentDate
MTD = CALCULATE( SELECTEDMEASURE(), DATESMTD( 'Date'[Full Date] ))
YTD = CALCULATE(SELECTEDMEASURE(),DATESYTD('Date'[Full Date]))
And then created a Date Period table to create a slicer measure
Date Periods =
vartodatdate = LASTDATE(FactWeighing[Weighing Date])
varWeekStart = CALCULATE(todatdate-WEEKDAY(todatdate,3),YEAR(FactWeighing[Weighing Date])=YEAR(todatdate))
varresults=
UNION(
ADDCOLUMNS(DATESMTD((FactWeighing[Weighing Date])),
"Type", "MTD","ID","2"),
ADDCOLUMNS(DATESYTD(FactWeighing[Weighing Date]),
"type","YTD","ID","3"),
ADDCOLUMNS(CALENDAR(WeekStart,todatdate),"Type","WTD","ID","1"))
return
results
Also created a relationship between Date table & the Dates period table.
I am not able to make the axis dynamic as per the user selection.
Can someone provide some suggestions.
Cheers