Year Computation

[djreyes]

Hello, 

I have a measure added in powerbi = ([QTY negative to positive max per item]*3)/365*([average qty]*1.2)

How do I make the 365 change that when they select a year filter it will become 730 if they selected 2018 to 2019, if they selected 2018-2019 it will become 1095 and so and so forth. 

I am not really sure how to do it. I have a transaction date column from 2018-2024 but its dynamic that it will refresh everyday so I need to consider that after 2024, new data from 2025 will be added.

Thank you so much!

[djreyes]

@samratpbi @lbendlin I did try it but when I filter 2018-2021 its showing 5 years instead of 4, how will i do the year multiply, it should be by days and not by year.

[samratpbi]

Hi, if you want to return no of years when selected, you can simply remove the + 1 in the measure. I thought you would need 730 if 1 year selected. Hence there is a + 1 with count years.

[djreyes]

Thank you so much! It works now, last question, so if a new year is added I dont need to do anything right?

[samratpbi · Accepted solution]

Right, it should autometically come into slicer and hence in report.

If this resolves your problem, then please mark this as solution, Thanks!

[lbendlin]

Don't use fixed numbers.  Have a calendar table in your data model and count the date rows for your selected years.

[samratpbi]

Hi, lets create a slicer on the years which you are going to select then create a measure below:

Years Selected =

IF(

    NOT(ISFILTERED(DynCol[Year])),

    1,

    COUNTROWS(

        SUMMARIZE(

            DynCol,

            DynCol[Year]

        )

    ) + 1

)

what it will do is, if no year selected, then it would return 1, otherwise it would return no of years selected + 1, i.e. if 1 year selected it would return 2 and so on.
now, you simply multiply this measure with 365 in your existing measure.
Below is the result:

when no year selected:

when 1 year selected:

Hope this resolves your problem,

If this helps to resolve your problem then please mark it as solution, Thanks!