Forum Discussion

SQUILES's avatar
SQUILES
Helper I
7 years ago
Solved

Previous Same WeekDay Averge

HI I have a challenge for my proyect, I need some help: I have one table with values for every day: date column and value column, i need to calculate the %umbral for each value.  %umbral = value /...
Need Help
  • SQUILES's avatar
    SQUILES
    7 years ago

    v-lili6-msft  Thanks, your correct, but I forget something very important ,

     

    I have a one column of TYPE, Wireless and Wireline, and I have a slicer to show only one of both,

    you DAX is taking both value, How I can modify your DAX to included just the values is selected by the slicer?

  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi SQUILES 

    First, you must know that calculated column and calculate table can't be affected by any slicer. 
    Notice:
    1. Calculation column/table not support dynamic changed based on filter or slicer.
    2. measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
    here is reference:
    https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
    Second, you could use a measure instead of it by this formula:

    Measure 2 = 
CALCULATE (
   AVERAGE('M7 Wireline_Wireless'[DATO_46]),
    FILTER (
       ALLSELECTED( 'M7 Wireline_Wireless'),
        DATEDIFF (
            'M7 Wireline_Wireless'[DATA_PERIODO],
            MAX('M7 Wireline_Wireless'[DATA_PERIODO]),
            MONTH
        ) = 1
            && WEEKDAY ( 'M7 Wireline_Wireless'[DATA_PERIODO], 1 ) = 2
    ))

    Best Regards,

    Lin

SQUILES's avatar
SQUILES
Helper I
7 years ago

HI

 

I did this measure:

Averg all Monday of Prev Month = CALCULATE(
AVERAGE('M7 Wireline_Wireless'[DATO_46]),
FILTER(PREVIOUSMONTH('M7 Wireline_Wireless'[DATA_PERIODO]),WEEKDAY('M7 Wireline_Wireless'[DATA_PERIODO],1)=2))

 

But i can't use this value to calculate a column.

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, SQUILES 

If you want to create a calculate column instead of measure, please try this formula

Column 2 = CALCULATE(
AVERAGE('M7 Wireline_Wireless'[DATO_46]),
FILTER('M7 Wireline_Wireless',DATEDIFF('M7 Wireline_Wireless'[DATA_PERIODO],EARLIER('M7 Wireline_Wireless'[DATA_PERIODO]),MONTH)=1&&WEEKDAY('M7 Wireline_Wireless'[DATA_PERIODO],1)=2))

Result:

 

Best Regards,

Lin

  • SQUILES's avatar
    SQUILES
    Helper I
    7 years ago

    v-lili6-msft  Thanks, your correct, but I forget something very important ,

     

    I have a one column of TYPE, Wireless and Wireline, and I have a slicer to show only one of both,

    you DAX is taking both value, How I can modify your DAX to included just the values is selected by the slicer?

    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      7 years ago

      hi SQUILES 

      First, you must know that calculated column and calculate table can't be affected by any slicer. 
      Notice:
      1. Calculation column/table not support dynamic changed based on filter or slicer.
      2. measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
      here is reference:
      https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
      Second, you could use a measure instead of it by this formula:

      Measure 2 = 
CALCULATE (
   AVERAGE('M7 Wireline_Wireless'[DATO_46]),
    FILTER (
       ALLSELECTED( 'M7 Wireline_Wireless'),
        DATEDIFF (
            'M7 Wireline_Wireless'[DATA_PERIODO],
            MAX('M7 Wireline_Wireless'[DATA_PERIODO]),
            MONTH
        ) = 1
            && WEEKDAY ( 'M7 Wireline_Wireless'[DATA_PERIODO], 1 ) = 2
    ))

      Best Regards,

      Lin