Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
guillaume17
New Member

dax filter power bi

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?

        

 

    

5 REPLIES 5
some_bih
Super User
Super User

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!








Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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].

Hi @guillaume18 

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)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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

Hi @guillaume18 I need to see pbi file to support you somehow.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.