PowerPivot DAX Date Lookup

0
votes

enter image description hereWould appreciate any help with this, I have a PowerPivot table, and I want to add a Dax column to find the sale by customer the previous week, for each customer on date dd/mm/yy find the value for (dd/mm/yy-7days). Example of what it would be in Excel using SUMIFS, not sure if possible in DAX ? Thanks in advance for your help. Gav

enter image description here

1
daxpowerpivot

1 Answers

1
votes

The right way to do any time intelligence calculations is to use a date table or as we call it date dimension. However, I imagine you need this for a quick "non model" calculation, so that case you can use the following DAX:

=
VAR DD =Table1[Date] - 7
        RETURN
            CALCULATE ( 
             FIRSTNONBLANK( Table1[Value]  , 1 ), 
                FILTER ( 
                    Table1, 
                    Table1[Date] = DD 
         
        )
   )

enter image description here

If this is the answer you were looking, please do not forget to mark this question as answered.