how to get past 3 year data and current year data based on year selected in year slicer

[Anonymous]

hi all,

    i have a typical report to generate in power bi, and complicated in structure:

if select filters year: 2019 and month: february, then o/p should be as below

                                                    2016          2017          2018                    mtd 2019       ytd 2019   

new patients count                        50              100            60                          20                   45

new patients amount                  1000          2000         1500                        1000               2000

continued patients count               40              46              70                        ****                   ****

continued patients amount            450         330              320                         ****                 ****

i will get the counts & amounts from database, but when i filter year as 2019,  i should get past 3 years data & current year data..

I could acheive displaying past 3 years data when year filter is selected.

But i couldnot acheive proper structure as i showed above. i'm getting as below:

                                                    2016          2017          2018                    mtd 2019       ytd 2019   

new patients count                        50              100            60                          20                   45

continued patients count               40              46              70                        ****                   ****

since i'm taking patient type count in rows (new patient/continued patient), they are coming row by row. but i need the amounts also in b/w them.how can i get according to the structure (avoid creating more measures)??

Any ideas on how to proceed?

Thanzz in advance..

[Anonymous]

Hi Cheenusing,

      thanx for reply..actually i can't share my data, due to some issues. i will explain you the scenario to understand it.

Actually filters are month & year, if month may & year 2019 are selected in slicers, then

new patients count & new patients amount, etc should show as below:

                                                    2016          2017          2018                    mtd 2019       ytd 2019   

new patients count                        50              100            60                          20                   45

new patients amount                  1000          2000         1500                        1000               2000

i.e: for 2016,2017,2018 it should show whole 2016,2017,2018 years data as above. but for currentyear(selected) it should show mtd (may1st to currentdate) & ytd(from jan2019 to currentdate). 

I know i can't put all in single matrix, so i kept previous years (2016,2017 & 2018) in one matrix & mtd2019 & ytd2019 in other. but if i do so i'm not getting new patients count & new patients amount row by row, i got new patients count&continued patientscount,etc(all counts) row by row.

how can i acheive the same structure..

any idea??

thanzz in advance..

[CheenuSing]

Hi @Anonymous ,

I tried working a solution for you.  What we can at best achieve is a table visual with the latest year and latest month of your data.

From my standpoint it is not possible to put  a slicer on year and month to achieve what you want.

1. Loaded a sample data called SalesData. with 2 columns SalesAmount and Order Date

     ( you can replace this with your data table )

2. Using the SalesData created the Calendar Table using Power Query.  Right Click on this table-->EditQuery-->Advanced Editor.  Copy and paste the code as blankquery in your model and rename it as CalendarTable.

3.  Created YearColumnTable using EnterData.  This table has the years of the CalendarTable and additional Rows as "MTD", "YTD".  Added an Index Column to this Table. Set the SortOrder for the Name column as Index.

4. Created measures

CountofOrders = Calculate(Count(SalesData[OrderDate]) )

Sales = Calculate ( sum(SalesData[SalesAmount]) )

5. Created Additional measures

ShowCount = 
VAR SelectedCol = MAX(YearColumnTable[Name])
VAR MaxYear = (MAX(CalendarTable[CalendarYear] )) 
VAR MaxMonth =  Calculate (MAX(CalendarTable[CalendarMonth]) ,ALL(CalendarTable),
                                    CalendarTable[CalendarYear] = MaxYear )

Return
SWITCH (TRUE(),
   SelectedCol=   "YTD",CALCULATE([CountofOrders],ALL(CalendarTable),                                                           CalendarTable[CalendarYear]= MaxYear
                              ) ,
   SelectedCol=   "MTD",CALCULATE([CountofOrders],FILTER(CalendarTable,                                                          CalendarTable[CalendarYear] = MaxYear &&  CalendarTable[CalendarMonth] = MaxMonth)) ,
   
   CALCULATE([CountofOrders],FILTER(CalendarTable,                                                        FORMAT (  CalendarTable[CalendarYear],"####") =SelectedCol) ))

ShowSales = 
VAR SelectedCol = MAX(YearColumnTable[Name])
VAR MaxYear = Calculate (MAX(CalendarTable[CalendarYear]) ,ALL(CalendarTable) )
VAR MaxMonth = Calculate (MAX(CalendarTable[CalendarMonth]) ,ALL(CalendarTable),
                                         CalendarTable[CalendarYear] = MaxYear )
Return
SWITCH (TRUE(),
   SelectedCol=   "YTD",CALCULATE([Sales],FILTER(CalendarTable,                                                         (  CalendarTable[CalendarYear]) = MaxYear)) ,
   SelectedCol=   "MTD",CALCULATE([Sales],FILTER(CalendarTable,                                                         (  CalendarTable[CalendarYear]) = MaxYear &&  CalendarTable[CalendarMonth] = MaxMonth)) ,
   CALCULATE([Sales],FILTER(CalendarTable,                                                        FORMAT( (  CalendarTable[CalendarYear]),"####") = SelectedCol)) )
     

6. Created a Matrix Table with  the following in the visualisation pane

     a ) Name  from YearColumnTable as Columns

     b)  ShowCount and ShowSales as Values

     c) Using the format (paint brush) on the visualisation pane go to values and set Show on Rows as On.

d)  In the SubTotals set Show ColumnTotals Off

I have attached the pbix file also for your reference.

Hope this works for you.

Cheers

CheenuSing

[Anonymous]

Hi  @CheenuSing,

      thanxz for your reply, but this is not the exact functionality i expected..

My expectation was if i filter year 2019 and month feb, from year & month filters.. i should see 2016,2017 and 2018 years data in matrix along with their prior years.. same if i selected 2018 year in filter, i should get 2015,2016,2017 years data..

the format should be like for 2019 years selected,

category            2016                         2017                                                2018

                         value      ||    value  prioryearvalue  %D   ||      value   prioryearvalue   %D 

-------------------------------------------------------------------------------------------------------

c1                      100              200         100              0.01 %       300          200               50%

c2                       500             600          500             0.99%        900           600              50%

If any idea, how can i acheive this in matrix. please help..

thanz in advance..

[CheenuSing]

hi @Anonymous ,

Are you trying to get the output as

New Patients count   -  for the month

New Patient Count - for the year

So if 2019 , February is selected the first row should show New Patients Count for Feb 2016, 2017, 2018 and 2019

And New Patient Count for the year should show 2016, 2017, 2018, 2019 ( Upto February )

is this understanding right ?

Can you put some data and your current pbix on One Drive / Google drive and share the link to find a solution.

Cheers

CheenuSing