Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Rephrase Below DAX

Hi All, 

Please go through below DAX measure that i have, please help me with rephrase this without changing it's results. also i want to ignore the merged query columns that i added from FiscalCalendarNew like "Month & Year", Year", :Fiscal Year", :MonthNameShort".


i want to rephrase below dax without changing/adding required columns 

Total Site = SWITCH (
    TRUE,
    SELECTEDVALUE ( 'FiscalCalendarNew'[MonthNameShort] ) = BLANK ()
        && SELECTEDVALUE ( 'FiscalCalendarNew'[Fiscal Year] ) <> BLANK (), CALCULATE (
        [Total Sites_],
        FILTER (
            'Tracker_J_All',
            MONTH ( 'Tracker_J_All'[Month & Year] )
                = MONTH(  TODAY ())            
                && 'Tracker_J_All' [Year]
                    = CALCULATE (
                        VALUE ( ( MAX ( 'FiscalCalendarNew'[Year] ) ) ),
                        FILTER (
                            'FiscalCalendarNew',
                            'FiscalCalendarNew'[Fiscal Year] = SELECTEDVALUE ( 'FiscalCalendarNew'[Fiscal Year] )
                        )
                    )
        )
    ),
    SELECTEDVALUE ( 'FiscalCalendarNew'[MonthNameShort]) = BLANK ()
        && SELECTEDVALUE ( 'FiscalCalendarNew'[Fiscal Year] ) = BLANK (), CALCULATE (
       [Total Sites_],
        FILTER (
            'Tracker_J_All'   ,
            MONTH ('Tracker_J_All'[Month & Year] )
                = MONTH ( TODAY () )
                && 'Tracker_J_All'[Year] = YEAR ( TODAY () )
        )
    ),
    SELECTEDVALUE ( 'FiscalCalendarNew'[MonthNameShort]) <> BLANK ()
        && SELECTEDVALUE ( 'FiscalCalendarNew'[Fiscal Year] ) <> BLANK (), CALCULATE (
        [Total Sites_],
        FILTER (
            'Tracker_J_All',
            MONTH ( 'Tracker_J_All'[Month & Year] )
                = CALCULATE (
                    MONTH ( MAX ( 'FiscalCalendarNew'[Date] ) ),
                    FILTER (
                        'FiscalCalendarNew',
                        'FiscalCalendarNew'[MonthNameShort] = SELECTEDVALUE ( 'FiscalCalendarNew'[MonthNameShort] )
                    )
                )
                && 'Tracker_J_All'[Year]
                    = CALCULATE (
                        VALUE ( MAX ( 'FiscalCalendarNew'[Year] ) ),
                        FILTER (
                            'FiscalCalendarNew',
                            'FiscalCalendarNew'[Fiscal Year] = SELECTEDVALUE ( 'FiscalCalendarNew'[Fiscal Year] )
                        )
                    )
        )
    )
)+0
Need Help
Tips and Tricks
Tutorial Requests

5 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    2 years ago

    Try the following :

     

    Total Site =
VAR IsMonthNameShortBlank = ISBLANK(SELECTEDVALUE('FiscalCalendarNew'[MonthNameShort]))
VAR IsFiscalYearNotBlank = NOT(ISBLANK(SELECTEDVALUE('FiscalCalendarNew'[Fiscal Year])))
VAR CurrentMonth = MONTH(TODAY())
VAR CurrentYear = YEAR(TODAY())
VAR MaxYearInFiscalCalendar = CALCULATE(
VALUE(MAX('FiscalCalendarNew'[Year])),
FILTER(
'FiscalCalendarNew',
'FiscalCalendarNew'[Fiscal Year] = SELECTEDVALUE('FiscalCalendarNew'[Fiscal Year])
)
)
VAR MaxDateInFiscalCalendar = CALCULATE(
MAX('FiscalCalendarNew'[Date]),
FILTER(
'FiscalCalendarNew',
'FiscalCalendarNew'[MonthNameShort] = SELECTEDVALUE('FiscalCalendarNew'[MonthNameShort])
)
)
RETURN
SWITCH(
TRUE,
IsMonthNameShortBlank && IsFiscalYearNotBlank, CALCULATE(
[Total Sites_],
FILTER(
'Tracker_J_All',
MONTH('Tracker_J_All'[Month & Year]) = CurrentMonth &&
'Tracker_J_All'[Year] = MaxYearInFiscalCalendar
)
),
IsMonthNameShortBlank && NOT(IsFiscalYearNotBlank), CALCULATE(
[Total Sites_],
FILTER(
'Tracker_J_All',
MONTH('Tracker_J_All'[Month & Year]) = CurrentMonth &&
'Tracker_J_All'[Year] = CurrentYear
)
),
NOT(IsMonthNameShortBlank) && IsFiscalYearNotBlank, CALCULATE(
[Total Sites_],
FILTER(
'Tracker_J_All',
MONTH('Tracker_J_All'[Month & Year]) = MONTH(MaxDateInFiscalCalendar) &&
'Tracker_J_All'[Year] = MaxYearInFiscalCalendar
)
)
) + 0
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks it's helped me a lot !!

      Can you also help me with creating another measure called "Previouse Month" using or referencing above measure ?


      i want to Previous month value when i am visulising the current month those should be get changed fiscal year and month 

      hope i am clear with my query

       

      • AmiraBedh's avatar
        AmiraBedh
        Super User
        2 years ago

        Try the following :

         

        Previous Month =
VAR IsMonthNameShortBlank = ISBLANK(SELECTEDVALUE('FiscalCalendarNew'[MonthNameShort]))
VAR IsFiscalYearNotBlank = NOT(ISBLANK(SELECTEDVALUE('FiscalCalendarNew'[Fiscal Year])))
VAR PreviousMonth = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1) 
VAR PreviousYear = IF(PreviousMonth = 12, YEAR(TODAY()) - 1, YEAR(TODAY()))
VAR MaxYearInFiscalCalendar = CALCULATE(
    VALUE(MAX('FiscalCalendarNew'[Year])),
    FILTER(
        'FiscalCalendarNew',
        'FiscalCalendarNew'[Fiscal Year] = SELECTEDVALUE('FiscalCalendarNew'[Fiscal Year])
    )
)
VAR MaxDateInFiscalCalendar = CALCULATE(
    MAX('FiscalCalendarNew'[Date]),
    FILTER(
        'FiscalCalendarNew',
        'FiscalCalendarNew'[MonthNameShort] = SELECTEDVALUE('FiscalCalendarNew'[MonthNameShort])
    )
)
VAR PreviousMaxDate = EOMONTH(MaxDateInFiscalCalendar, -1)
RETURN
SWITCH(
    TRUE,
    IsMonthNameShortBlank && IsFiscalYearNotBlank, CALCULATE(
        [Total Sites_],
        FILTER(
            'Tracker_J_All',
            MONTH('Tracker_J_All'[Month & Year]) = MONTH(PreviousMaxDate) &&
            'Tracker_J_All'[Year] = YEAR(PreviousMaxDate)
        )
    ),
    IsMonthNameShortBlank && NOT(IsFiscalYearNotBlank), CALCULATE(
        [Total Sites_],
        FILTER(
            'Tracker_J_All',
            MONTH('Tracker_J_All'[Month & Year]) = PreviousMonth &&
            'Tracker_J_All'[Year] = PreviousYear
        )
    ),
    NOT(IsMonthNameShortBlank) && IsFiscalYearNotBlank, CALCULATE(
        [Total Sites_],
        FILTER(
            'Tracker_J_All',
            MONTH('Tracker_J_All'[Month & Year]) = MONTH(PreviousMaxDate) &&
            'Tracker_J_All'[Year] = YEAR(PreviousMaxDate)
        )
    )
) + 0