cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Regular Visitor

Re: Forward looking Count DAX logic and dates

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
 

View solution in original post

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Forward looking Count DAX logic and dates

@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-Y...


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Forward looking Count DAX logic and dates

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] )
    )
)

forecast.JPG

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

Highlighted
Regular Visitor

Re: Forward looking Count DAX logic and dates

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

Highlighted
Community Support
Community Support

Re: Forward looking Count DAX logic and dates

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

Highlighted
Regular Visitor

Re: Forward looking Count DAX logic and dates

Rena,

Perhaps visual is a better example. In this example, I am only getting 15day trip counts.

 

Here I have my input table with Trip#, the date the trip was created, and the date that the trip will take place.

In the middle, I have the virtual tables I need for each date to do my calculations. Red values are excluded because they are either not in the 15day date criteria or have already taken place. 

The resulting table shows the date and 15day trip counts based on my intermediate virtual table. 

 

Trip Count Example 15day.png

 

I hope this helps explain my issue.

Thanks again for your help.

-Peter

Highlighted
Community Support
Community Support

Re: Forward looking Count DAX logic and dates

Hi @PeterBlatchley ,

First, please create a Date table. Then create a measure as below, put the date field in Date table in X axis of visual...

Trip Count =
VAR _days = 15
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Trip'[TripNum] ),
        FILTER (
            ALL ( 'Trip' ),
            'Trip'[Created Date] <= MAX ( 'Date'[Date] )
                && 'Trip'[Trip Date] > 'Trip'[Created Date]
                && 'Trip'[Trip Date]
                    <= MAX ( 'Date'[Date] ) + _days
        )
    )

Best Regards

Rena

Highlighted
Regular Visitor

Re: Forward looking Count DAX logic and dates

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
 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors