Forum Discussion
Two time Frames on Same Graph
wherdzik well, you have to build the logic in the measure which you will use in the visuals.
As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel
Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist
Can you help me with the logic? I tried getting ChatGPT to help me but it is not working. Here is my dax code that it suggested:
AveragePatientsPerDoctorYTD =
VAR SelectedDate = MAX('SEP YTD'[Service Date].[Day])
VAR YTDStartDate = CALCULATE(
MIN('SEP YTD'[Service Date].[Day]),
ALL('SEP YTD'[Service Date]),
'SEP YTD'[Service Date].[Year] = YEAR(SelectedDate)
)
VAR TempTable =
ADDCOLUMNS(
FILTER(
'SEP YTD',
'SEP YTD'[Service Date] >= YTDStartDate && 'SEP YTD'[Service Date] <= SelectedDate
),
"DistinctPatientsPerDate", CALCULATE(COUNTROWS(SUMMARIZE('SEP YTD', 'SEP YTD'[Patient Name])))
)
VAR TotalPatients = SUMX(TempTable, [DistinctPatientsPerDate])
VAR TotalDays = COUNTROWS(TempTable)
RETURN
IF(
TotalDays > 0,
DIVIDE(TotalPatients, TotalDays),
BLANK()
)Here is my data set. Esentially I want to select February and have, in this case Average Visits Per day on a YTD basis.
Here is what my data looks like for reference. I just cannot get it to work.