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