Forum Discussion

PeterBlatchley's avatar
PeterBlatchley
Helper I
6 years ago
Solved

Forward looking Count DAX logic and dates

I am working with a travel dataset that has essentially 3 columns as shown below.

 

CreatedInitial DepartTripNum
6-15-206-25-201
6-16-206-26-202

 

I would like to create a table for trip counts for the next 15, 30 and 45 days. I can do this easily in real-time. However, I am looking to have historical data to plot in a line chart. To do this I need to exclude all the trips that are greater than the current date in the given date context based on the trip creation date. The chart below is what I am looking to create.

 

Date15 Day Trip Count30 Day Trip Count45 Day Trip Count
6-1-20

10

2025
6-2-20

11

2529
6-3-20

15

3135
6-4-201424

42

 

I am really struggling with the DAX logic on this issue. For context, I have created a dates table that is related to the initial departure date. 

 

Thanks,

Peter

Need Help
  • PeterBlatchley's avatar
    PeterBlatchley
    6 years ago

    Rena,

     

    I was missing the && logic to get the filter to work correctly. Thanks for your help with this question. 

     

    One additional change I needed to make was to add the following:

    Trip Count =
VAR _days = 15
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Trip'[TripNum] ),
        FILTER (
            ALL ( 'Trip' ),
            'Trip'[Created Date] <= MAX ( 'Date'[Date] )
   -------->    && 'Trip'[Trip Date] >= Max ( 'Date'[Date])
                && 'Trip'[Trip Date] > 'Trip'[Created Date]
                && 'Trip'[Trip Date]
                    <= MAX ( 'Date'[Date] ) + _days
        )
    )
     
    This removes all the trips that have already occurred. Otherwise, I get a cumulative trip count.
     
    Thanks again
    -Peter
     

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    PeterBlatchley , not very clear. but you can create formulas like these with a date table

     

    Rolling 15 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),15,Day))

    Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),30,Day))

    Rolling 45 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),45,Day))

     

    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 :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi PeterBlatchley ,

    Please try to create a measure as below and add Forecast in Analytics pane, later check if it can achieve your requirement:

    Trip Count =
CALCULATE (
    DISTINCTCOUNT ( 'Trip'[TripNum] ),
    FILTER (
        ALL ( 'Trip' ),
        'Trip'[Created] <= MAX ( 'Date'[Date] )
            && 'Trip'[Initial Depart] = MAX ( 'Date'[Date] )
    )
)

    Forecasting in Power BI

    Apply forecasting

    If it is not applicable for your scenario, please provide more sample data and your expected result(include caculation logic of per count and examples). Do you want to forecast the trip count in the next 45 days or get the trip count in past?

    Best Regards

    Rena

    • PeterBlatchley's avatar
      PeterBlatchley
      Helper I
      6 years ago
      Do you want to forecast the trip count in the next 45 days or get the trip count in past?

       

      Hi Rena,

       

      Thanks for the reply. As you mentioned, I am looking to get the trip count in the past. 

      • For each date
        • count the trips for the next 15, 30 and 45 days 
        • in the context of the trip was created on or before the date

      Thanks,

      Peter

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi PeterBlatchley ,

        I'm not sure whether I understand your requirement correctly. For example, if the current data in chart is on 2020/8/20, then you want to get the trip count in 2020/8/20, 2020/8/5, 2020/7/21 and 2020/7/6? May I know whether the table exist the data with old date?

        Best Regards

        Rena