Forum Discussion

wherdzik's avatar
wherdzik
Helper III
2 years ago

Two time Frames on Same Graph

Hi built this dashboard and the user wants two differnet time frames. So when I pick the month some of the statistics that that have a YTD prefacing it.  They have to be on a YTD basis so If I selected APRIL the YTD Avg Visit Per Day visual, for example, would be January to April.  

Is there a way to do this by selecing one month and the system knows some of them are YTD and some are only one month, the selction.

 

 

Need Help

4 Replies

    • wherdzik's avatar
      wherdzik
      Helper III
      2 years ago

      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. 

       

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    wherdzik That is the worst DAX expression I have seen. Thanks ChatGPT 😁

     

    Well as I suggested, check out the time intelligence videos to understand and then it will be super easy for you to solve everything. Also, there is a video on how to calculate YTD numbers.

  • wherdzik's avatar
    wherdzik
    Helper III
    2 years ago

    I created a Calendar Table.  But I still do not know how to get the YTD amount in there.  I am struggling quite a bit to get this to work.  

     

    I tried to use chatGPT now that I have this table but its still not working. 

     

    AverageVisitsPerDoctorForSelectedMonthOrYTD = 
VAR SelectedMonthEnd = EOMONTH(MAX('Calendar'[Date]), 0)
VAR YearStart = STARTOFYEAR('Calendar'[Date])
VAR ProviderFilter = ALLSELECTED('SEP YTD'[Provider])
VAR TotalPatients =
    CALCULATE(
        DISTINCTCOUNT('SEP YTD'[Patient Name]),
        ProviderFilter,
        'SEP YTD'[Service Date] >= YearStart && 'SEP YTD'[Service Date] <= SelectedMonthEnd
    )
VAR TotalServiceDays =
    CALCULATE(
        DISTINCTCOUNT('SEP YTD'[Service Date]),
        ProviderFilter,
        'SEP YTD'[Service Date] >= YearStart && 'SEP YTD'[Service Date] <= SelectedMonthEnd
    )
RETURN
IF(
    TotalServiceDays > 0,
    DIVIDE(TotalPatients, TotalServiceDays),
    BLANK()
)

     

    For example when I select January in the filter I should expect the Month output and the YTD output be the same. 

     

    Sorry I am just so new at this that I am struggling pretty badly.

     

    This is my date table.