CODEWITHSUNDEEP
powerbipowerbi-desktop
PeguREM
PeguREM

Reputation: 11

Current year vs Last year : Filter on ISO week number

I am trying to set up a report on conversions this year vs last year. This formula has worked on another report but cannot show the conversions from last year when applied to this client.

When just filtering on a particular week last year, it shows conversions, so there must be something wrong with my measure.

LY Conversion = 
CALCULATE(
SUM('Krogh_Søksrapport'[All_Conversions]), 
PREVIOUSYEAR('Date table'[Date]),
'Date table'[ISOWeeknumber] IN VALUES('Date table'[ISOWeeknumber]))

Any obvious errors? I've tried to dismantle it piece by piece but can't find the error.

All help is appreciated :)

UPDATE for Ozan:

enter image description here

We have a date table with one-to-many relationship

Date table looks like this: enter image description here

SOLVED: All we had to do was mark the date table as the the date table, so that the "previousyear"-function used our date table for its date logic.

Upvotes: 0

Views: 963

Answers (2)

PeguREM
PeguREM

Reputation: 11

All we had to do was

  1. mark the date table as the the date table, so that the "previousyear"-function used our date table for its date logic.
  2. Thats it!

Man beat machine this day!

Visual solution

Upvotes: 1

Ozan Sen
Ozan Sen

Reputation: 2615

Please test this one:

Update()

Version_01

LY Conversion =
CALCULATE (
    SUM ( 'Krogh_Søksrapport'[All_Conversions] ),
    FILTER (
        ALL ( 'Date table'[Date], 'Date table'[ISOWeeknumber] ),
        SAMEPERIODLASTYEAR ( 'Date table'[Date] )
            && 'Date table'[ISOWeeknumber] IN VALUES ( 'Date table'[ISOWeeknumber] )
    )
)

Version_02

LY Conversion =
CALCULATE (
    SUM ( 'Krogh_Søksrapport'[All_Conversions] ),
    CALCULATETABLE (
        ALL ( 'Date table'[Date], 'Date table'[ISOWeeknumber] ),
        SAMEPERIODLASTYEAR ( 'Date table'[Date] ),
        'Date table'[ISOWeeknumber] IN VALUES ( 'Date table'[ISOWeeknumber] )
    )
)

Upvotes: 0

Related Questions