Measure doesn't work for records before specific date

[BartoszKx]

Hi,

When I was trying to calculate average number of players in selected date range, i found out that measures doesn't work correctly for dates before 09-2014. I created this temporary measure:

tmp = CALCULATE(AVERAGEX('GameStatistics', [Avg players]), YEAR('GameStatistics'[Date]) = 2014, MONTH('GameStatistics'[Date]) = 😎

This returns blank, but if we replace 8 with 9 (September 2014) it results with 17,61

'GameStatistics' table contains data for August 2014: 

'GameStatistic' is also in relation with Date Table:

I also tried this formula:

tmp = CALCULATE(AVERAGEX('GameStatistics', [Avg players]), 'GameStatistics'[Year/Month] = "2014/8")

with same result as before.

What might be the case for this weird behaviour?
I would be very grateful for any help.


 

[Anonymous]

Hi @BartoszKx , rajendraongole1, thank you for your prompt reply!

Please create a simple measure to check if you could return month values in a table:

Best regards,

Joyce

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

[BartoszKx]

Hi @Anonymous,

Thank you for your reply. This measure doesn't work in report. I can't retrive month from date column:

 

Here's sample data and report:
https://drive.google.com/file/d/1NdD5gDj1GWpa-6E-Zyk0yuBtH2wkP01K/view?usp=sharing

[Anonymous]

Hi  @BartoszKx，

Thank you for your additional information, use All function to ignore any filters that might have been applied.

tmp = CALCULATE(AVERAGEX('GameStatistics','GameStatistics'[Avg players]), ALL(GameStatistics),YEAR('GameStatistics'[Date]) = 2014, MONTH('GameStatistics'[Date]) =8)

Best regards,

Joyce

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

[rajendraongole1]

Hi @BartoszKx - Can you check there is no missing values, blanks in Date column also incorrect datatype also results blank.check the format in Game statistics table too as you have given relationship 

if possible share sample data in drive to look over that give us to analysis and share expectations.

Thank you

[BartoszKx]

Hi @rajendraongole1,

Thanks for your reply. Dates columns in 'GameStatistics' and 'DateTable', have same format and data type. There aren't any blank values in this columns either.

Link for sample data and pbix file:
https://drive.google.com/file/d/1NdD5gDj1GWpa-6E-Zyk0yuBtH2wkP01K/view?usp=sharing

After this post had been created i realised that tmp value somehow depends on filter applied with slider in report page. In sample data this measure behaves differently than in orignal pbix file. When I set slider like this:

tmp measure has value. When begin of the slider is set to 2014-06-23, then tmp value is blank: