cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 / 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)

 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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Frequent Visitor

## Re: Previous Same WeekDay Averge

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

Highlighted
Community Support Team

## Re: Previous Same WeekDay Averge

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Member

## Re: Previous Same WeekDay Averge

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

Frequent Visitor

## Re: Previous Same WeekDay Averge

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)

Frequent Visitor

## Re: Previous Same WeekDay Averge

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.

Community Support Team

## Re: Previous Same WeekDay Averge

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Previous Same WeekDay Averge

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

Highlighted
Community Support Team

## Re: Previous Same WeekDay Averge

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

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

Announcements

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### November 2019 Community Highlights

Get an overview of the events and great community content from November.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)