cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SQUILES Frequent Visitor
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)

 

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

 

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
SQUILES Frequent Visitor
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?

Capture.JPG

View solution in original post

Highlighted
Community Support Team
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.

View solution in original post

6 REPLIES 6
gckcmc Member
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

 

SQUILES Frequent Visitor
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) 

 

 

SQUILES Frequent Visitor
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
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:

3.JPG4.JPG

 

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.
SQUILES Frequent Visitor
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?

Capture.JPG

View solution in original post

Highlighted
Community Support Team
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.

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

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

November 2019 Community Highlights

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

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)