topic Compare with "where" and dateadd (same period last year) in DAX Commands and Tips

Compare with "where" and dateadd (same period last year)

eriweste — Fri, 02 Sep 2022 06:27:40 GMT

Hi all,

Need assistant with creating a compare for values created this month with same period -1 year (last year)

- Im able to get the result when not using filters (where)

- Date columns exist

Goal

Create a dashboard that presents rows that differs more then 20 % (+ or -) from same period last year

This report are to be used to compare the data before the invoicing process starts

Dataset

Need to compare (ex) the period Aug (08-22) with Aug (08-21)

The TSQL/ps1 would be like (simplified - do not laugh :))

$1 = Select _consumption where readingdate = Aug 2022 and quantitycodevalue = 'kvarh' and readingstatus = '2'

$2 = Select _consumption where readingdate = Aug 2021 and quantitycodevalue = 'kvarh' and readingstatus = '3'

if $1 $2 > Diff then 20 % of value of $1 = Display res

(plz note that readingstatus differs (2 = Approved, 3 = Billed))

Any ideas

erik

Re: Compare with "where" and dateadd (same period last year)

daXtreme — Fri, 02 Sep 2022 08:33:26 GMT

Have a look at this pattern.

Re: Compare with "where" and dateadd (same period last year)

eriweste — Fri, 02 Sep 2022 08:45:47 GMT

Hi,

Thanks for the reply. The DATE "issue" is handled (getting the correct period to compare)

Main issue is to correctly using the "where" perspective (filter), if not the SUM of all _consumptions are being presented. I need the individual rows to be compared (Kvarh, Hög, låg etc)