Forum Discussion

BartoszKx's avatar
BartoszKx
Regular Visitor
2 years ago

Measure doesn't work for records before specific date

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.


 

Need Help

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      BartoszKx
      Regular Visitor
      2 years ago

      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:

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.