cancel
Showing results for
Did you mean:
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.

 Created Initial Depart TripNum 6-15-20 6-25-20 1 6-16-20 6-26-20 2

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.

 Date 15 Day Trip Count 30 Day Trip Count 45 Day Trip Count 6-1-20 10 20 25 6-2-20 11 25 29 6-3-20 15 31 35 6-4-20 14 24 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

7 REPLIES 7
Highlighted
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://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...

Proud to be a Super User!

Highlighted
Community Support

## Re: Forward looking Count DAX logic and dates

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

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

## Re: Forward looking Count DAX logic and dates

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.

I hope this helps explain my issue.

-Peter

Highlighted
Community Support

## Re: Forward looking Count DAX logic and dates

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### 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