Mean of a mesure filter by date

[Anonymous]

Hello everyone,

I got two measures one call Date where there are only the year or 0 and one called M0 where there are number.

I would like to have in a report the year and the mean of M0 which corresponding to the date. I post a picture it would be more easy to understand:

at the left is what i have and at the right (the green) is what I would like to have in my report.

For exemple here I want 2020 and the mean of (42,6,19,21,2)

I try with an average fct but it don't accet measure. Il also try with a divide fct but same thing, the measure are not accepted.

Can you help me please?

Thank you!

[v-yetao1-msft · Accepted solution]

Hi @Anonymous 

Please correct me if I wrongly understood your question .

You said you want use the Date Measure of the Feuil1 to filter the data . But for slicer , measure cannot be a field to put in it .So we can create a calculated column to achieve the same result with Date Measure .

Date =

VAR currentYear =

    IF (

        MONTH (  Feuil1[Production Date Finale]  ) < 4,

        YEAR (  Feuil1[Production Date Finale]  ) - 1,

        YEAR (  Feuil1[Production Date Finale]  )

    )

VAR monthsInCurrentYear =

    COUNTAX (

        FILTER (

            ALLSELECTED ( Feuil1[Name],Feuil1[Production Date Finale],Feuil1[Insurance Name] ),

            IF (

                MONTH ( Feuil1[Production Date Finale] ) < 4,

                YEAR ( Feuil1[Production Date Finale] ) - 1,

                YEAR ( Feuil1[Production Date Finale] )

            ) = currentYear

        ),

        MONTH ( Feuil1[Production Date Finale] )

    )

RETURN

    IF (

        monthsInCurrentYear = 12

            && currentYear > 2018,

        IF (

            MONTH ( Feuil1[Production Date Finale] ) < 4,

            YEAR (  Feuil1[Production Date Finale]  ) - 1,

            YEAR (Feuil1[Production Date Finale]  )

        ),

       0

    )

The effective is as shown :

Then create a new table ’Axis’ and extract column[Date] from table’Feuil 1’ ,then use the column[Date] and measure[M0] to calculate the average value .

Axis = DISTINCT(Feuil1[Date])

Result Measure =

VAR newtable =

    SUMMARIZE (

        Feuil1,

        Feuil1[Production Date Finale],

        "@date", SELECTEDVALUE(Feuil1[Date]),

        "@m0", [M0]

    )

VAR groupnewtable =

    FILTER (

        GROUPBY ( newtable, [@date], "@avg", AVERAGEX ( CURRENTGROUP (), [@m0] ) ),

        [@date] = MAX ( 'Axis'[Date] )

    )

RETURN IF ( ISFILTERED ( 'Axis'[Date] ), AVERAGEX ( groupnewtable, [@avg] ) + 0 )

The effective is as shown :

Then use the 'Axis'[Date] as a slicer to filter data in table ’Axis’ .In slicer filters pane , set the value for 'Axis'[Date] is not equal to 0.

The final result is as shown:

I have attached my pbix file, you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

Result Measure =

IF ( SELECTEDVALUE ( 'Table'[Date] ) = 0, BLANK (), AVERAGE ( 'Table'[M0] ) )

https://www.dropbox.com/s/vbc8oh0a93g8d81/thovla.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

@Jihwan_Kim Thank you but as I said my column Date and M0 are measure, i just write them on excel to be easier to understand. And as they are measure the fct don't work

Thank you

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Thanks.

[Anonymous]

@Jihwan_Kim 

Thank you, i put my dropbox link in this reply, it's my first time using it so if the link don't work please notice me. Maybe I should put my Date and M0 into column and not in measure but I didn't succed to do it.

https://www.dropbox.com/s/ll9brjlqelux8ib/Test.pbix?dl=0

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for sharing.

I am not sure if I understood your data model correctly, but please check the below picture and the link down below.

I created an axis table in order to present 2019 and 2020 as a column.

Result Measure =

VAR newtable =

SUMMARIZE (

Feuil1,

Feuil1[Production Date Finale],

"@date", [Date],

"@m0", [M0]

)

VAR groupnewtable =

FILTER (

GROUPBY ( newtable, [@date], "@avg", AVERAGEX ( CURRENTGROUP (), [@m0] ) ),

[@date] = MAX ( 'Axis'[Date] )

)

RETURN

IF ( ISFILTERED ( 'Axis'[Date] ), AVERAGEX ( groupnewtable, [@avg] ) + 0 )

https://www.dropbox.com/s/tct0pkypz9c7dgr/Test.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

@Jihwan_Kim Wouau thank you so much it's perfect! just it is possible in the axis table to put in Date only the year that are present on the Date Measure of the Feuil1? Like something automatical, so if I change the filter it will put only the years present and not all,

Thank you!

[Anonymous]

@Jihwan_KimMoreover sometimes I got plenty of the same date and in this case it doesn't work

https://www.dropbox.com/s/ta62jng1l38n1ea/Test-1.pbix?dl=0

[Jihwan_Kim]

Hi, 

please try the below.

I still cannot understand the measure [date] and [m0], but please try the below whether it works or not. 

I tried the below and it showed the result, however I cannot know whether it is correct or not.

Result Measure =

VAR newtable =

ADDCOLUMNS(SUMMARIZE (

Feuil1,

Feuil1[Production Date Finale]),

"@date", [Date],

"@m0", [M0]

)

VAR groupnewtable =

FILTER (

GROUPBY ( newtable, [@date], "@avg", AVERAGEX ( CURRENTGROUP (), [@m0] ) ),

[@date] = MAX ( 'Axis'[Date] )

)

RETURN

IF ( ISFILTERED ( 'Axis'[Date] ), AVERAGEX ( groupnewtable, [@avg] ) + 0 )

[Anonymous]

@Jihwan_Kim I will explain you what I'm trying to do, I got a bunch of date (month and year) in my column DATE1 and there is a value M0 coresponding to each date. I got two other columns called Name and Name2 which allow me to do filter. My goal is to do the mean of all my month year by year. I would like to have for exemple in a report 2019 and the mean of all the value of the month of 2019, then 2020 and the mean of all 2020 values. If it's possible on the report I will have only the year present on my Date Column depending on my filter. If with my filter there are none month of 2019 but only month of 2020. I would like to have in my report only the year 2020. I also don't want to put year occured before 2019.

[amitchandak]

@Anonymous , Create a date table and have a year in that or year is your table and analyze sum(Table[M0])  with that

Year = year([date])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.