Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

How to get average reference two last specific day in a calculated table?

Hi guys, i need a help!

I'd like to receive the average from the two last Mondays in my month and fill the current Monday. It will be a kind of forecast in my business case. At the moment, i have the average of days, but i dont know how to get the average in a calculated column. Follow example below. Any help is appreciated. 

 

Dashboards
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    7 years ago

    Hi Anonymous, 

     

    You can create a measure below: 

     

    Measure =
VAR CurrentDate =
    MAX ( 'Table1'[Data Venda] )
VAR MinDate =
    CALCULATE (
        MIN ( 'Table1'[Data Venda] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            MONTH ( 'Table1'[Data Venda] ) = MONTH ( CurrentDate )
        )
    )
RETURN
    IF (
        CurrentDate = MinDate,
        CALCULATE (
            AVERAGE ( Table1[Total Vendas] ),
            FILTER (
                ALLSELECTED ( 'Table1' ),
                WEEKNUM ( 'Table1'[Data Venda] )
                    >= WEEKNUM ( CurrentDate ) - 2
                    && WEEKNUM ( 'Table1'[Data Venda] ) < WEEKNUM ( ( CurrentDate ) )
            )
        ),
        BLANK ()
    )

     

    Best Regards,
    Qiuyun Yu 

2 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous, 

     

    You can create a measure below: 

     

    Measure =
VAR CurrentDate =
    MAX ( 'Table1'[Data Venda] )
VAR MinDate =
    CALCULATE (
        MIN ( 'Table1'[Data Venda] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            MONTH ( 'Table1'[Data Venda] ) = MONTH ( CurrentDate )
        )
    )
RETURN
    IF (
        CurrentDate = MinDate,
        CALCULATE (
            AVERAGE ( Table1[Total Vendas] ),
            FILTER (
                ALLSELECTED ( 'Table1' ),
                WEEKNUM ( 'Table1'[Data Venda] )
                    >= WEEKNUM ( CurrentDate ) - 2
                    && WEEKNUM ( 'Table1'[Data Venda] ) < WEEKNUM ( ( CurrentDate ) )
            )
        ),
        BLANK ()
    )

     

    Best Regards,
    Qiuyun Yu 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello v-qiuyu-msft it resolved my problem! Thanks a lot!