Forum Discussion

Pikachu-Power's avatar
Pikachu-Power
Icon for Impactful Individual rankImpactful Individual
5 years ago
Solved

DAX optimization: ValueMonth minus ValueMonthBefore

Hi all,

 

i have the following measure that works fine. but is there a way to write it more compact? I have created two sepatated month and year tables and integrated the SELECTEDVALUE in the formula. Just calculating "ValueMonth minus ValueMonthBefore". Is it may possible to write this with using the connected Calender Table and without the separated month and year tables?

 

 

Measure =
 
CALCULATE(SUM(Tabel1[Value]),
Tabel1[Categorie] = "A",
FILTER(Tabel1, Tabel1[Year] = SELECTEDVALUE(Measure_Year[Year])),
FILTER(Tabel1, Tabel1[Month] = SELECTEDVALUE(Measure_Month[Month_ID])))
-
 
IF(SELECTEDVALUE(Measure_Month[Month_ID]) = 1,
 
CALCULATE(SUM(Tabel1[Value]),
Tabel1[Categorie] = "A",
FILTER(Tabel1, Tabel1[Year] = SELECTEDVALUE(Measure_Year[Year])-1),
FILTER(Tabel1, Tabel1[Month] = 12)),
 
CALCULATE(SUM(Tabel1[Value]),
Tabel1[Categorie] = "A",
FILTER(Tabel1, Tabel1[Year] = SELECTEDVALUE(Measure_Year[Year])),
FILTER(Tabel1, Tabel1[Month] = SELECTEDVALUE(Measure_Month[Month_ID])-1))
)
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Pikachu-Power 

     

    have a try with your connected Calendar

    test =
VAR CurSales =
    CALCULATE ( SUM ( Tabel1[Value] ), Tabel1[Categorie] = "A" )
VAR PreSales =
    CALCULATE (
        SUM ( Tabel1[Value] ),
        Tabel1[Categorie] = "A",
        DATEADD ( Calendar[Date], -1, MONTH )
    )
RETURN
    CurSales - PreSales

      

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Pikachu-Power 

     

    There are other ways, just modify your current one

     

    Measure =
VAR CurMonth =
    SELECTEDVALUE ( Measure_Month[Month_ID] )
VAR CurYear =
    SELECTEDVALUE ( Measure_Year[Year] )
VAR CurSales =
    CALCULATE (
        SUM ( Tabel1[Value] ),
        Tabel1[Categorie] = "A",
        FILTER ( Tabel1, Tabel1[Year] = CurYear && Tabel1[Month] = CurMonth )
    )
VAR PreSales =
    IF (
        CurMonth = 1,
        CALCULATE (
            SUM ( Tabel1[Value] ),
            Tabel1[Categorie] = "A",
            FILTER ( Tabel1, Tabel1[Year] = CurYear - 1 && Tabel1[Month] = 12 )
        ),
        CALCULATE (
            SUM ( Tabel1[Value] ),
            Tabel1[Categorie] = "A",
            FILTER (
                Tabel1,
                Tabel1[Year] =  CurYear
                    && Tabel1[Month] = CurMonth - 1
            )
        )
    )
RETURN
    CurSales - PreSales

     

    • Pikachu-Power's avatar
      Pikachu-Power
      Icon for Impactful Individual rankImpactful Individual
      5 years ago

      Hi Vera,

      many thanks for your idea. Is it also possible to use the connected calender table and get rid of the separated year / month table? i think it will be than more difficult to show the previous month/year, right? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Pikachu-Power 

         

        have a try with your connected Calendar

        test =
VAR CurSales =
    CALCULATE ( SUM ( Tabel1[Value] ), Tabel1[Categorie] = "A" )
VAR PreSales =
    CALCULATE (
        SUM ( Tabel1[Value] ),
        Tabel1[Categorie] = "A",
        DATEADD ( Calendar[Date], -1, MONTH )
    )
RETURN
    CurSales - PreSales