CODEWITHSUNDEEP
powerbidax
Andy Williams
Andy Williams

Reputation: 907

SQL TO DAX Conversion

New to Power BI and needing some help on a Dax Conversion. The Date 20191130 is being passed in via a Slicer from another page So I am not sure how to Set up my dax in a such a way to read the following below:

Current Value

SELECT SUM(value) FROM Table
Where DateKey BETWEEN 20181201 AND 20191130

Previous Value

SELECT SUM(value) FROM Table
Where DateKey BETWEEN 20171201 AND 20181130

(Current Value - Previous Value)/(Previous Value)

Upvotes: 0

Views: 108

Answers (1)

CR7SMS
CR7SMS

Reputation: 2584

Assuming Slicer Date is in Date format, you will have to create two different calculations:

Current Value: 

 Current Value =
            VAR End1 = Table[SlicerDate]
            VAR Start1 = DATE(YEAR(End1)-1,MONTH(End1),DAY(End1))+1
            VAR End2 = DATE(YEAR(End1)-1,MONTH(End1),DAY(End1))
            VAR Start2 = DATE(YEAR(End1)-2,MONTH(End1),DAY(End1))+1
    RETURN CALCULATE(COUNT('Table'[Name]),'Table'[Datekey]>=Start1 && 'Table'[Datekey]<=End1)

Previous Value:

Previous Value =
        VAR End1 = Table[SlicerDate]
        VAR Start1 = DATE(YEAR(End1)-1,MONTH(End1),DAY(End1))+1
        VAR End2 = DATE(YEAR(End1)-1,MONTH(End1),DAY(End1))
        VAR Start2 = DATE(YEAR(End1)-2,MONTH(End1),DAY(End1))+1
RETURN CALCULATE(COUNT('Table'[Name]),'Table'[Datekey]>=Start1 && 'Table'[Datekey]<=End1)

If your slicer date is in numeric or text format:

Current Value:

Current Value =
        VAR End1 = Date(Left(Table[SlicerDate],4),Right(left(Table[SlicerDate],6),2),right(Table[SlicerDate],2)
        VAR Start1 = DATE(YEAR(End1)-1,MONTH(End1),DAY(End1))+1
        VAR End2 = DATE(YEAR(End1)-1,MONTH(End1),DAY(End1))
        VAR Start2 = DATE(YEAR(End1)-2,MONTH(End1),DAY(End1))+1
RETURN CALCULATE(COUNT('Table'[Name]),'Table'[Datekey]>=Start1 && 'Table'[Datekey]<=End1)

Previous Value:

Previous Value =
        VAR End1 = Date(Left(Table[SlicerDate],4),Right(left(Table[SlicerDate],6),2),right(Table[SlicerDate],2)
        VAR Start1 = DATE(YEAR(End1)-1,MONTH(End1),DAY(End1))+1
        VAR End2 = DATE(YEAR(End1)-1,MONTH(End1),DAY(End1))
        VAR Start2 = DATE(YEAR(End1)-2,MONTH(End1),DAY(End1))+1
RETURN CALCULATE(COUNT('Table'[Name]),'Table'[Datekey]>=Start1 && 'Table'[Datekey]<=End1)

Hope this helps.

Upvotes: 1

Related Questions