CODEWITHSUNDEEP
powerbidax
Zulfikar S Khan
Zulfikar S Khan

Reputation: 41

Prev years YTD sales Dax formula

I am having some difficulties in creating a dax formula for calculating prev yr YTD sales.

I have written a formula but the same is not working.

I need to calculate the performance % yr over yr by comparing YTD sales of current year to YTD of prev yr sales.

any help would be appreciated

Sales sameperiod = 
VAR first_date =
    FIRSTDATE ( DATEADD ( 'Date'[Date], -12, MONTH ) )
VAR last_date =
    LASTDATE ( DATEADD ( 'COGS Data'[Invoice Date], -12, MONTH ) )
RETURN
    IF (
        ISBLANK ( first_date ) || ISBLANK ( last_date ),
        BLANK (),
        CALCULATE (
            SUM ( 'COGS Data'[Final Unit Cost] ),
            DATESBETWEEN ( 'Date'[Date], first_date, last_date )
        )
    )

Upvotes: 0

Views: 313

Answers (1)

karlis ssl
karlis ssl

Reputation: 143

there are multiple ways, but my go-to is creating a Date table, I assume you already have it. Then you would create relationship to Fact table from DateKey, and a new matrix visual with rows from Date Table, for example Date and Month. And Measure would be like -

Revenue last year = IF(
HASONEVALUE ('Date'[Month]),
IF ( 
    SUM ('COGS Data'[Final Unit Cost] ) <> BLANK(),
    CALCULATE (
        SUM ( 'COGS Data'[Final Unit Cost] ),
        SAMEPERIODLASTYEAR ('Date'[Date])
    )
),
CALCULATE (
    SUM ( 'COGS Data'[Final Unit Cost] ),
    DATESBETWEEN (
        'Date'[Date],
        EDATE ( MIN ('Date'[Date]), -12 ),
        EDATE ( MAX ('COGS Data'[Invoice Date]), -12 )
    )

Upvotes: 1

Related Questions