Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Compare dates based on values

Hello! I am fairly new to PowerBI but loving it so far but I am alittle stuck on something. I would like to setup a stacked area chart (or any other chart) which will compare dates where there is rain vs no rain recorded and display the COUNT of visitors based on those dates. Any help or guidance will be greatly appreciated! 

 

CountRain

 

 

 

Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    What is the output you want?

     

    Can you share source data in table format

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Here's my data model:

    In the Weather table , I created a calculated column:

     

    Number of visiter = 
CALCULATE(
    MAX('Count'[Count])
)

     

    Then, I created the visualization:

     

    Best regards,
    Lionel Chen

    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
      6 years ago

      Hi v-lionel-msft thanks for the replay and help! I followed your steps but for some reason I am getting the same SUM amount for both RAIN and NO RAIN in my graphs. I noticed your date format is different than mine, not sure if that matters. Also note that one day could have rain in the morning but not at night so the same day could have a 0.00 amount recorded. Any ideas?

       

       

      • v-lionel-msft's avatar
        v-lionel-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        My model is as follows:

        Is your data model the same as mine? In other words, are the two tables a 1: * relationship?

        For example, let ’s say 1/1/2020 at 4 am, 8 am without rain, and 2 pm with rain, and the number of visitors on that day is 100. Is this true?

        If yes, you can do like this:

        First, Ctrl+left-click to choose [Date] column and [Rain Validation] column, then, "Remve Duplicate rows":

        Second, create a calculated coumn in weather table:

        Number = 
CALCULATE(
    MAX('Count'[Count]),
    ALLEXCEPT(
        'Count',
        'Count'[Date]
    )
)

        Third, create visual:

         

        Best regards,
        Lionel Chen

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