Forum Discussion

adam_b's avatar
adam_b
Frequent Visitor
5 years ago

Matrix - Multiple Rows

Hi, Hoping someone can point me in the right direction or what I am missing? When I have one row in my Matrix the values display correct - however as soon as I have multiple Rows to allow drill...
Need Help
adam_b's avatar
adam_b
Frequent Visitor
5 years ago

Hi Anonymous 

 

Difficult to share data as its being pulled straight from SQL and for some reason If I make an example data in Excel it seems to work ok?

 

See below, first screenshot is with just one row - however I've noticed the Spend (Same Period Last Year) still is not correct.

Second screenshot is when I add in multiple rows the previous year value decreases.

 

This is for periods 01/08/2020 - 31/08/2020 - so the Spend (Same Period Last Year) should be showing around £94k but even with one row it is still incorrect.
If I change the Date Filter to show 01/08/2019 - 31/08/2019 I get £94k but with one row its showing £88k and with 3 rows it shows £32k.


I dont know if its my data, measure or I'm just doing something wrong?

 

My Measure is 

 

Spend(Same Period Last Year) = CALCULATE(SUM(Query1_2[LINE_AMOUNT]),SAMEPERIODLASTYEAR('Date'[Date]))

 

adam_b's avatar
adam_b
Frequent Visitor
5 years ago

Wow. Really confused dont know if its because I have a Date Slicer which Syncs between pages, however Just created a standalone date slicer and matrix table and it seems to work...

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi adam_b 

    I think you need to build a unrelated Date table to be your slicer.

    If your Date table which you used to build date slicer is related to your Fact table, the time intelligence function may show wrong result due to the filter.

     

    Best Regards,

    Rico Zhou

     

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