locked
How to get day to day changes from a 60 day report RRS feed

  • Question

  • I have a report that goes back 60 days. That is it has 60 days of information rolling. I have a table with a column starting date and one that is an ending date. Most of the time that's fine because I want to look at just the last 60 days. But let's say I had CSV files of the report generated on a bunch of different days (all with 60 day periods). How can I get the incremental values? Essentially all the columns with values I would want to subtract from a particular period. I can sort of visualize it but what would be the best way to set that up? Thanks. 
    Sunday, February 5, 2017 10:02 AM

Answers

  • Hi AlexMartini,

    Do your reports come from different resource table? If it is, you should combine them into one table, then you use the measure above to get what you want.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, February 8, 2017 9:15 AM

All replies

  • Hi AlexMartini, 

    First, you’d better create a calendar table, create a relationship between the calendar and fact table.

    Then I create a measure to calculate the rolling 60 day average and get the result shown in second snapshot.

    60days_sum = CALCULATE(SUM(Test[Value]),FILTER(ALL(Test),AND(Test[Date]<=MAX(Test[Date]),Test[Date]>(MAX(Test[Date])-60))))

    Create a pivot table and select the date as row level, the measure as value level. If this doesn’t solve your issue, please share sample data and expected result for further analysis. Thanks a lot.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Monday, February 6, 2017 2:15 AM
  • Hi Angelia. It's not so much about the measure but rather the setup that I should use to get all the reports in there? Should I just copy in each data set? They will have overlapping dates. For example. Jan 1st to Feb 28th and the next report will be January 7th and March 7th. The difference between those reports is the incremental volume. 
    Monday, February 6, 2017 10:57 AM
  • Hi AlexMartini,

    Do your reports come from different resource table? If it is, you should combine them into one table, then you use the measure above to get what you want.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, February 8, 2017 9:15 AM