Forum Discussion

Paz2019's avatar
Paz2019
Regular Visitor
6 years ago

Problem with Variation measure

Hello everyone.

I have a Date table called Dim_Date2 and it doesn't connected to my Fact table Sales for others reasons. So, I have to filter date in each measure.

I have the next measure to calculate Sales and Previous Sales:

 

Sales:=CALCULATE(SUM(Fact_Sales[SalesAmount]), FILTER(Fact_Sales,Fact_Sales[SalesDate]>=[MinimumDate]&&Fact_Sales[SalesDate]<=[MaximumDate])) 

Previous Sales:=CALCULATE(Fact_Sales[Sales],DATEADD(Dim_Date2[Date],-[SelectedDays],DAY))
 
MaximumDate:MAX(Dim_Date2[Date]) 

MinimumDate:MIN(Dim_Date2[Date]) 

SelectedDays:CALCULATE(DISTINCTCOUNT(Fact_Sales[KeySalesDate]),FILTER(Fact_Sales,Fact_Sales[SalesDate]>=[MinimumDate]&&Fact_Sales[SalesDate]<=[MaximumDate]))

 


And the variation is the subtraction of both.

Previous Sales measure works if I select an specific date, but if I select the same date with a Year, month and day... it doesn't work .

 

I hope you can help me.

 

DAX
Need Help
SSAS

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi

    You can try:  dateadd(LASTDATE(Dim_Date2[Date]),-1,day)

     Previous Sales = calculate([Sales],dateadd(LASTDATE(Dim_Date2[Date]),-1,day))

     

    If I answer your question, please mark my post as a solution, this will also help others.
    Please give Kudos for support.

    Tomas Santandreu Polanco |Principal Business Intelligence Consultant
    www.designmind.com