Forum Discussion

MatthewMcL's avatar
MatthewMcL
Frequent Visitor
8 years ago
Solved

Transaction level date to Rolling Time Summary

Hello, I am very new to PowerBI, DAX and M Query but I am very familiar with general SQL Queries, Excel and general programming. I’ve been asked to develop something in powerbi and I’ve been hittin...
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hmm, not sure if you want to get the following results.

     

     

    If it is, first create a calcuated column and use the following DAX to add Rank Number

     

    Rank = RANKX(Sold,Sold[Date],,ASC,Dense)

    Then, create another calucated column and use the DAX below to get the cumulative sum in 2 days by item name

    2Days_Sum = CALCULATE(SUM(Sold[Sold]),ALLEXCEPT(Sold,Sold[Item Name]),Sold[Rank]<=EARLIER(Sold[Rank])&&Sold[Rank]>EARLIER(Sold[Rank])-2)
MatthewMcL's avatar
MatthewMcL
Frequent Visitor
8 years ago

Sorry I did a poor job explaining my problem. I tried to follow the solution you linked too and I ran into the same problem I've been having.

 

I don't want to look at the latest time frame but at 4 days from the date within the row.

 

I've been trying to apply time intelligence methods to the query but I keep running into the same issue.

 

Here is an example of a two day rolling sum.

2daysum = CALCULATE(
        SUM(Sheet1[Sold]),
        FILTER(ALL('Sheet1'), 'Sheet1'[Date] >= Sheet1[Date]-2 && Sheet1[Date]<Sheet1[Date])
)

 

So I want to sum everything between the date 2 days ago and the current date in the row.

 

When I do this I just recieve blank results for every row.

 

Seward12533's avatar
Seward12533
Icon for Solution Sage rankSolution Sage
8 years ago

Time Intelligence functions need date tables. For this work you need a date table that is linked to your data and then you compare the date in your table to date in the date table. 

 

2daysum = CALCULATE(
        SUM(Sheet1[Sold]),
        FILTER(ALL('DateDim'), 'Sheet1'[Date] >= DateDim[Date]-2 && Sheet1[Date]<DateDim[Date])
)

  • MatthewMcL's avatar
    MatthewMcL
    Frequent Visitor
    8 years ago

    Hmm, I am still getting blank values after creating the Date Dimension table and linking it to the Date column in my dataset.

    My Date Dimension table is just unique dates between the minimum date and maximum date within my original dataset.

     

    2daysum = CALCULATE(
            SUM(Sheet1[Sold]),
           FILTER(ALL('DateTable'),'Sheet1'[Date]>=DateTable[Date]-2 && Sheet1[Date]<DateTable[Date]
    ))

    • Seward12533's avatar
      Seward12533
      Icon for Solution Sage rankSolution Sage
      8 years ago

      Please check two thing

       

      1. Date Table a conguous list of days (no missing days)
      2. Both the DATE Key in the Date Table and the Date Column in the Fact Table are rormatted to Date Only and Not Date/Time

      I tested the same formula on a data set I have and worked as expected