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

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.

Highlighted
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

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 Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,991)