Compare dates based on values

[Anonymous]

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

[v-lionel-msft]

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]

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]

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.

[Anonymous]

Hey @v-lionel-msft 

So I have a separate DATE table which I use in my data model but when I tried to directly connect weather to COUNT it can only be set as many-to-many relationship mainly because there many duplicate dates in both, you can download my datasets in my previous posts, appreciate your time in helping! 

[v-lionel-msft]

Hi @Anonymous ,

It is not necessary to establish a common date table, and directly establish a many-to-many relationship between the two tables, and then calculate.

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.

[amitchandak]

What is the output you want?

Can you share source data in table format

[Anonymous]

Well the output would be to find dates that have Rain/No Rain and match it to the COUNTS of visitors so I can compare.  

COUNTS TABLE 

https://drive.google.com/file/d/1kgWqxVabtGhLvmhq3u-rigbnrOqQ3vjT/view?usp=sharing 

WEATHER TABLE

https://drive.google.com/file/d/103I6N7G5C-GU-zFa5qyx-v-ZM6LPHUzq/view?usp=sharing