Matrix Table - Show Next Level

[Anonymous]

Hi Data Nuts!

I am having an issue I haven't been able to solve and am eliciting your assistance, any input would be greatly appreciated.

I have a Matrix that is showing correct past year when there is data in the current period, when there is not, it displays a blank instead of the data.  (In my examples I have added + 0 to show the blanks as a zero).

Here are the Measures:

ArrivalsTAV =

    CALCULATE(

        COUNTA(MergedReportData[ID]) + 0

ArrivalsTAV-1 =

CALCULATE ([ArrivalsTAV],ALLEXCEPT (

        MergedReportData,

        MergedReportData[TravelPurpose],

        MergedReportData[TravelPurposeGroup]),

    ( PARALLELPERIOD(MergedReportData[ArrivalDt],-12,MONTH))) + 0

When 2017 for example is selected I see the data for the current period and prior (2016)

2016 2017

When I change my slicer to 2018 these values should shift to prior years as the top two rows do

Note the top two rows behaved correctly and the bottom row (Americas Cup) did not, there is no data in 2018 for this category and the prior periods became blanks.

Also Note the the totals are correct for the prior years and behind the scene the data is being calculated while not being displayed.

PS - The + 0 added to the measures has been removed and tested with the result of blanks instead of zero's

The date is correct and there are no gaps, this I can't see as the issue as all other calculations are correct when current period data exists

Thanks in advance for any advice!!

[v-xicai]

Hi @Anonymous ,

You can create column Year in table MergedReportData, and create calculated table Year dimension , and keep it to be unrelated to source data table.  Then create measures ArrivalsTAV and ArrivalsTAV -1 .

Year= YEAR(MergedReportData[ArrivalDt])

Year dimension = VALUES(MergedReportData[Year])

ArrivalsTAV =
IF (
   SELECTEDVALUE ( MergedReportData[Year] )
       = SELECTEDVALUE ( 'Year dimension'[Year] ),
   CALCULATE (
       COUNTA(MergedReportData[ID]) + 0,
       FILTER (
           ALLSELECTED ( MergedReportData ),
           MergedReportData[Year] = SELECTEDVALUE ( 'Year dimension'[Year] )
       )
   )
)

ArrivalsTAV -1 =
IF (
   SELECTEDVALUE ( MergedReportData[Year] )
       = SELECTEDVALUE ( 'Year dimension'[Year] ),
   CALCULATE (
       COUNTA(MergedReportData[ID]) + 0,
       FILTER (
           ALLSELECTED ( MergedReportData ),
           MergedReportData[Year]
               = SELECTEDVALUE ( 'Year dimension'[Year] ) - 1
       )
   )
)

Best Regards,

Amy

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

[Anonymous]

I am giving this one a shot and will let you know how it goes, thank you!

[parry2k]

@Anonymous do you have date dimension in your model? Is it marked as date table?

[parry2k]

@Anonymous can you try one small quick change, replace parallelperiod with dateadd, let's what result we get and then we can go from there.

[Anonymous]

Thanks for the idea - changed to DATEADD and the effect is the same