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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

I'm sure there's more elegant ways of doing this, but I would decompose the date field into it's component values.  There are functions that give you Month, Day, Year based on the date cell value.  From there you can also get day of the week.

https://docs.microsoft.com/en-us/powerquery-m/date-functions

 

Once you have all of that, then you can compute measures with the filters you wish (all mondays in June) or whatever.

https://docs.microsoft.com/en-us/power-bi/desktop-measures

 

  • SQUILES's avatar
    SQUILES
    Helper I
    7 years ago

    Thanks for you suggest,

     

    I decompse the date in weekdays (1 for sunday, 2 for monday, etc...) and month number (1 for jan, 2 for feb, etc), now How can calculate the average for all monday of the previous month.

                     Monday_Avrg = AVERAGE( ALL MONDAYS OF PREVIOUS MONTH) 

                     Tuesday_Avrg = AVERAGE( ALL MONDAYS OF PREVIOUS MONTH) 

     

     

    • 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