Previous Same WeekDay Averge

[SQUILES]

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 / average (same weekday of previous month)

Sample: 

%umbra for 7/1/2019 = 5,267,250 / average (every monday value of june)

%umbra for 7/2/2019 = 5,768,867 / average (every tuesday value of june)

Any help will be appreciate, if need more info please comment...

date	value
7/1/2019	5,267,250
7/2/2019	5,768,867
7/3/2019	5,962,147
7/4/2019	3,484,193
7/5/2019	4,648,212
7/6/2019	3,658,692
7/7/2019	2,550,949
6/1/2019	4,076,488
6/2/2019	2,715,579
6/3/2019	5,205,302
6/4/2019	5,763,867
6/5/2019	5,770,751
6/6/2019	5,703,688
6/7/2019	5,578,933
6/8/2019	3,908,630
6/9/2019	2,611,256
6/10/2019	5,315,556
6/11/2019	5,707,597
6/12/2019	5,544,984
6/13/2019	5,335,150
6/14/2019	5,446,567
6/15/2019	4,102,067
6/16/2019	2,915,821

[SQUILES · Accepted solution]

@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?

[Anonymous]

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]

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]

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]

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 · Accepted solution]

@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 · Accepted solution]

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