Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

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
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
 
amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.