topic Re: dax filter power bi in DAX Commands and Tips

dax filter power bi

guillaume17 — Fri, 08 Sep 2023 13:34:09 GMT

Good morning
I am new here.
I calculated the total amount of sales. then I created variables to find the sales of the past year. and make the formula dynamic.

VAR sells =

CALCULATE ( SUM ( BookingLines[LineSell] ) )

VAR OGGI =

( TODAY () )

VAR AnnoScorso =

YEAR ( TODAY () ) - 1

RETURn CALCULATE (

SUM ( BookingLines[LineSell] ),

(

BookingLines[LineCreationDate]

<= ( DATE ( AnnoScorso, MONTH ( OGGI ), DAY ( OGGI ) ) ) ) )

now I have to ensure that the sales for the year 2022 only take into account the reservations for the year 2022 with the 'BookingLine'[startdate] filter

how can i proceed?

Re: dax filter power bi

some_bih — Sun, 10 Sep 2023 09:44:35 GMT

Hi guillaume17

try to rewrite your measure as following

VAR sells =

CALCULATE ( SUM ( BookingLines[LineSell] ) )

VAR OGGI =

( TODAY () )

VAR AnnoScorso =

YEAR ( TODAY () ) - 1

VAR start_year=DATE ( AnnoScorso, MONTH ( OGGI ), DAY ( OGGI ) )

RETURN CALCULATE (

SUM ( BookingLines[LineSell] ),

BookingLines[LineCreationDate]

<= start_year

)

Did I answer your question? Kudos appreciated / accept solution!

Re: dax filter power bi

Anonymous — Tue, 12 Sep 2023 10:58:06 GMT

hi some_bih I copied your formula but at table level, the column remains empty. I want this year's sales to only take into account bookings from last year using the filter 'Bookinglines[stardate].

Re: dax filter power bi

some_bih — Tue, 12 Sep 2023 11:38:01 GMT

Hi Anonymous

Model is not available, so please try below.

VAR sells =

CALCULATE ( SUM ( BookingLines[LineSell] ) )

VAR OGGI =

( TODAY () )

VAR AnnoScorso =

YEAR ( TODAY () ) - 1

RETURn CALCULATE (

SUM ( BookingLines[LineSell] ),

AnnoScorso)

Re: dax filter power bi

Anonymous — Tue, 12 Sep 2023 12:36:53 GMT

i have to use the filter 'Bookinglines[stardate].

Re: dax filter power bi

some_bih — Tue, 12 Sep 2023 13:06:21 GMT

Hi Anonymous I need to see pbi file to support you somehow.