Forum Discussion

Sylvine_Wyz's avatar
Sylvine_Wyz
Helper IV
5 years ago

Dax - relatives dates

Good morning

I want to highlight:

-the average BF from the previous month

-the average BF for the current month

-the average BF for the following month

So I put relative date filters on my 3 visuals.

But I want to create a DAX formula so as not to use the filters.

Is there a DAX formula that exists?

Thank you in advance.

Sylvine_Wyz_0-1603794316759.png

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Sylvine_Wyz , Not very clear you can use time intelligence with date table

    example

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
    last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
    this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
    last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
    next MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],1,MONTH)))

    next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hey, @Sylvine_Wyz

    According to your description, I created data to reproduce your scenario. The pbix file is attached at the end.

    Mesa:

    c1.png

    You can create measures as follows.

    Current Month = 
CALCULATE(
    AVERAGE('Table'[Value]),
    FILTER(
        ALL('Table'),
        YEAR([Date])=YEAR(TODAY())&&
        MONTH([Date])=MONTH(TODAY())
    )
)
    Previous Month = 
CALCULATE(
    AVERAGE('Table'[Value]),
    FILTER(
        ALL('Table'),
        YEAR([Date])=YEAR(EOMONTH(TODAY(),-1))&&
        MONTH([Date])=MONTH(EOMONTH(TODAY(),-1))
    )
)
    Following Month = 
CALCULATE(
    AVERAGE('Table'[Value]),
    FILTER(
        ALL('Table'),
        YEAR([Date])=YEAR(EOMONTH(TODAY(),1))&&
        MONTH([Date])=MONTH(EOMONTH(TODAY(),1))
    )
)

    Result:

    c2.png

    Best regards

    Allan

    If this post helps,then consider Accepting it as the solution to help other members find it faster.

    • Sylvine_Wyz's avatar
      Sylvine_Wyz
      Helper IV
      5 years ago

      Hello @v-alq-msft
      Thanks for your help.

      But i need to use filter in Iso_Pays (Fr, ES, Be...)
      Currently, if i select the segment FR or Es, it's the same BF.

      Can you help me ?

      Thanks

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        5 years ago

        Hi, Sylvine_Wyz 

         

        You may create a single column table 'Slicer' containing 'BF', 'Fr', 'ES' and so on. Then you may try modifying the measure like below to see if it helps.

        Current Month =
IF (
    HASONEVALUE ( 'Slicer'[Iso_pays] ),
    SWITCH (
        SELECTEDVALUE ( 'Slicer'[Iso_pays] ),
        "BF",
            CALCULATE (
                AVERAGE ( 'Table'[BF] ),
                FILTER (
                    ALL ( 'Table' ),
                    YEAR ( [Date] ) = YEAR ( TODAY () )
                        && MONTH ( [Date] ) = MONTH ( TODAY () )
                )
            ),
        "Fr",
            CALCULATE (
                AVERAGE ( 'Table'[Fr] ),
                FILTER (
                    ALL ( 'Table' ),
                    YEAR ( [Date] ) = YEAR ( TODAY () )
                        && MONTH ( [Date] ) = MONTH ( TODAY () )
                )
            ),
        "ES",
            CALCULATE (
                AVERAGE ( 'Table'[ES] ),
                FILTER (
                    ALL ( 'Table' ),
                    YEAR ( [Date] ) = YEAR ( TODAY () )
                        && MONTH ( [Date] ) = MONTH ( TODAY () )
                )
            )
    )
)

         

        Best Regards 

        Allan

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.