Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
)
)
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] )
)
)
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
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.
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
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.
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
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
)
)
@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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |