Forum Discussion

andicornejo12's avatar
andicornejo12
Icon for Helper II rankHelper II
2 years ago
Solved

Formula not working without adding the year on the visual

Hello all,

 

I have a formula (not time intelligence) to show the Calendar Week (CW) of the previous year (PY).

 

However the formula doesnt work when I dont have the year in the table, please see below. Is there any tweak I can do to it so that it will still show regardless if the year field is there or not.

 

Here the formula:

 

PY CW =

VAR _selectedYear = SELECTEDVALUE('Date'[Year])

 

VAR _selectedWeek = SELECTEDVALUE('Date'[Week])

 

RETURN

 

IF(

    NOT ISINSCOPE('Date'[Week]),

    CALCULATE(

        SUM(Orders[Sales]),

        FILTER(

            ALL('Date'),

            'Date'[Year] = _selectedYear -1

        )

    ),

    CALCULATE(

        SUM(Orders[Sales]),

        FILTER(

            ALL('Date'),

            'Date'[Year] = _selectedYear -1 &&

            'Date'[Week] = _selectedWeek

        )

    )

)

 

Also why in the table that is working the totals are not displayed?

 

Thank you very much!!

DAX
Help
  • Rupak_bi's avatar
    Rupak_bi
    2 years ago

    Its not wirking as you are using Selected value. So if there is no year column, the selected value is blank. Insted selected value, you may try max of date. Is also not showing the total as at total level your conditions doesnot matches. 

  • v-zhangti's avatar
    v-zhangti
    2 years ago

    Hi, andicornejo12 

     

    You can try the following formula.

    PY CW =
VAR _selectedYear =
    MAX ( 'Date'[Year] )
VAR _selectedWeek =
    MAX ( 'Date'[Week] )
RETURN
    IF (
        NOT ISINSCOPE ( 'Date'[Week] ),
        CALCULATE (
            SUM ( Orders[Sales] ),
            FILTER ( ALL ( 'Date' ), 'Date'[Year] = _selectedYear - 1 )
        ),
        CALCULATE (
            SUM ( Orders[Sales] ),
            FILTER (
                ALL ( 'Date' ),
                'Date'[Year] = _selectedYear - 1
                    && 'Date'[Week] = _selectedWeek
            )
        )
    )

    If this does not solve your problem, please provide more information about the data.

     

    Best Regards,

    Community Support Team _Charlotte

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

     

2 Replies

  • Rupak_bi's avatar
    Rupak_bi
    Icon for Super User rankSuper User
    2 years ago

    Its not wirking as you are using Selected value. So if there is no year column, the selected value is blank. Insted selected value, you may try max of date. Is also not showing the total as at total level your conditions doesnot matches. 

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    2 years ago

    Hi, andicornejo12 

     

    You can try the following formula.

    PY CW =
VAR _selectedYear =
    MAX ( 'Date'[Year] )
VAR _selectedWeek =
    MAX ( 'Date'[Week] )
RETURN
    IF (
        NOT ISINSCOPE ( 'Date'[Week] ),
        CALCULATE (
            SUM ( Orders[Sales] ),
            FILTER ( ALL ( 'Date' ), 'Date'[Year] = _selectedYear - 1 )
        ),
        CALCULATE (
            SUM ( Orders[Sales] ),
            FILTER (
                ALL ( 'Date' ),
                'Date'[Year] = _selectedYear - 1
                    && 'Date'[Week] = _selectedWeek
            )
        )
    )

    If this does not solve your problem, please provide more information about the data.

     

    Best Regards,

    Community Support Team _Charlotte

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