Sum Values Every Nth Day

[cheid_4838]

I created the below table that shows tractors, distance traveled each day and various fuel totals. I would like to to create a column that will show the distance traveled every 2 or 3 days for each tractor. I want to compare that total to the Total Miles (Full) column to decide whether fuel stops could be eliminated.  Is there an easy way to sum the distance every 2 or 3 days?  Thanks.

[v-pnaroju-msft]

Hi cheid_4838,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft]

Thankyou, @jgeddes, @Ashish_Mathur, for your response.

Hi cheid_4838,

We sincerely appreciate your inquiry submitted through the Microsoft Fabric Community Forum.

Based on my understanding of the issue, please find attached a screenshot along with a sample pbix file, which may assist in resolving the matter:

If you find our response helpful, kindly consider marking it as the accepted solution and provide kudos. This will aid other community members encountering similar queries.

If the response does not fully address your requirements, we kindly request you to provide sample data that clearly illustrates your issue or question in a usable format (not as a screenshot). Please ensure that the data does not contain any sensitive information or unrelated content. Additionally, please specify the expected outcome based on the sample data provided.

Should you have any further queries, please do not hesitate to reach out to the Microsoft Fabric community.

Thank you.

[jgeddes]

Using DATESINPERIOD in a CALCULATE function may work for you. As an example...

Last X Days = 
CALCULATE(
    SUM('Table'[Distance]),
    DATESINPERIOD('Table'[Date], SELECTEDVALUE('Table'[Date]), -2, DAY)
)

You could also use a parameter to dynamically set the number of days if you wanted to switch between 2 or 3. (Or any number you want.)

[cheid_4838]

That did not work.  Regardless of the number I enter the logic does not sum and the value is the same for each date.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[jgeddes]

I should have mentioned that if you are using a date table in your model you will need to ensure that the dates referenced in the measure align with the dates used in the visualization, which can vary if you are using a date model.