Multiple time periods comparison in Power BI

[Anonymous]

Hi everyone,

I have a question regarding a multiple-year comparison report in Power BI and DAX. The first five columns in the below table show the example data, the sixth column shows the two requirements, and the last column shows whether a plan _ID in the first column meets the requirement. I hope to count the number of plan_IDs which meet both requirements for a specific season (e. g. Spring 2018).

As you can see from the last column, Spring 2018 has 3 "yes" while Spring 2019 has 6. Therefore, for Spring 2019, the "Count of Plans for This Year" is 6 while the "Count of Plans for The Last Year" is 3, as shown in the table below. The table is what I want to have.

My question is how to count the plans that meet the two requirements for a specific season/season_number such as Spring 2019/190.

I have been struggling in this situation for a long time. Any ideas or suggestions will be greatly appreciated.

[Anonymous]

I appreciate your inputs.

I used your suggested measures to calculate numbers of "This year" and "Last year" but got the different results from my posted idea result table. The measures I used and the results I got are shown below.

YTD Plans = CALCULATE(COUNT('Example Data'[Plan_ID]),DATESYTD('Date'[Date],"12/31"))

Last YTD Plans = CALCULATE(COUNT('Example Data'[Plan_ID]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

This year Plans = CALCULATE(COUNT('Example Data'[Plan_ID]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))

Last year Plans = CALCULATE(COUNT('Example Data'[Plan_ID]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Last to last YTD Plans = CALCULATE(COUNT('Example Data'[Plan_ID]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Plans = CALCULATE(COUNT('Example Data'[Plan_ID]),dateadd('Date'[Date],-1,Year)) 

This Year = CALCULATE(COUNT('Example Data'[Plan_ID]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))

Last Year = CALCULATE(COUNT('Example Data'[Plan_ID]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

YTD Plans  = CALCULATE(COUNT('Example Data'[Plan_ID]),DATESYTD('Date'[Date],"12/31"), 'Example Data'[Season] = "Spring")

Last YTD Plans = CALCULATE(COUNT('Example Data'[Plan_ID]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"), 'Example Data'[Season] = "Spring")

Season                 This Year        Last Year     YTD Plans     Last YTD Plans

Spring 2021          4                   6

Spring 2020          6                   8

Spring 2019          8                   4

Spring 2018          4                   1

All Plan_IDs whose Plan_Dates before 10/16/2020 for Spring 2021 need to be counted. For example, Plan_Date 12/15/2019 for Spring 2021 has to be counted., even the Plan_Date was in 2019 not in 2020.

Hope the above explanations make sense to you, and looking forward to your further help. Thanks again!

[Anonymous]

// First off, you should not
// store all your data in one
// big table. This is not a good
// or professional approach to
// data modeling inside Power BI.
// Indeed, such models are hiding
// a lot of traps for the untrained
// eye and, contrary to what one might
// think, people not fully conversant
// with DAX and data modeling should
// never use such models. Well, nobody
// should use such models but for quick
// prototyping or very quick and rough
// analysis. So, please change your model
// so that it's correct and following 
// Best Practices. Here's a link to what
// a good model should be:
// https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
// Having said all that, here's a measure
// that very likely does what you want
// (and it works in all circumstances as
// long as you extend the definition of
// counting in the right way) and is working
// with this "unhealthy" model. To do time-based
// calculations you need a date table. Let's
// call it 'Calendar'. This table will be DISCONNECTED.

[Plan Count] =
var __today = TODAY()
var __todaysYear = YEAR( __today)
var __out =
    COUNTROWS(
        Filter(
            YourTable,
            var __year = YourTable[Year]
            var __planDate = YourTable[Plan_Date]
            var __upperLimit = 
                dateadd(
                    filter(
                        ALL( Calendar[Date] ),
                        Calendar[Date] = __today
                    ),
                    __year - __todaysYear - 1,
                    YEAR
                )
            return
                __planDate <= __upperLimit
        )
    )
return
    __out

[Anonymous]

Thanks a lot for your inputs!

I used your suggested solution to calculate "This Year Plan Count" and got the exact numbers I need. Could you please help teach me how to calculate the "Last Year Plan Count"?

This Year Plan Count =

var __today = TODAY()

var __todaysYear = YEAR( __today)

var __out =

    COUNTROWS(

        Filter(

            'Example Data',

            var __year = 'Example Data'[Year]

            var __planDate = 'Example Data'[Plan_Date]

            var __upperLimit =

                dateadd(

                    filter(

                        ALL( 'Date'[Date] ),

                        'Date'[Date] = __today

                    ),

                    __year - __todaysYear - 1,

                    YEAR

                )

            return

                __planDate <= __upperLimit

        )

    )

return

    __out

Season                 This Year Plan Count      

Spring 2021          5                   

Spring 2020          4                 

Spring 2019          6                   

Spring 2018          3                   

Looking forward to your further help!

[amitchandak]

@Anonymous , As long as Year/date is play . You can use date and year table and get yealy data like given below. Now filter season by a slicer or add a filter

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below




This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Add filter

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"), Date[Season] = "Spring")
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"), Date[Season] = "Spring")