Calendar value

[ChadSmith22]

Hi Everyone,

I have a report that i need to create.

i have an excel file with a series of questions.

Example:

Question 1: Enter the duration of the challenge. User chooses two date fields (1 Jan 2022 ) to ( 4th Mar 2022 )

Question 1a : Enter the rating of the challenge. User enters low moderate high (or 1, 2, 3)

What i need to solve in Power BI.

I need to then for Jan, Feb, Mar for Question 1: give it a consistent score so

Jan-1, Feb-1 Mar-1 (if user chooses low)

I then have 10 other questions. and then i need to sum all scores in the column and take an average so i can build a heatmap

What do i need to do?

- create a calendar table first?

- link the question table to the calendar tabl?

thanks

[ChadSmith22]

its not job related, ie not paid work i just want to build a heatmap by month to see the answers, its easier..

do you have any thoughts?

[hnguy71]

Hi @ChadSmith22 ,

You would likely need to build two disconnected dimension tables, one for your dates and one for your choices. You'll also need to build fact table that captures the user inputs.

Honestly, it's a bit hard to visualize the requirement without understanding the full picture of the problem statement.

[ChadSmith22]

[ChadSmith22]

Does the image help? i have a table in Power Called Questions. I do capture the title, start and end dates. But now i need to see at say Jan-22 am i green or amber based on all the questions..

[hnguy71]

Hi @ChadSmith22 ,

The image helped on the expected input and output but I'm still confused on the questions and its relevance to your data.

I've created a sample output for you to play around.

Here's the pbix file: https://1drv.ms/u/s!An8CCFsOzw0uhQkwdIIQEmV3Aqqt?e=DA8Uqf

[ChadSmith22]

thankyou  - however the issue is that 

question 1 - i need to report it green for jan, feb, mar

question 2 - i need to report it amber for feb to jun

(refer to above screenshot i sent)

same for question 3....

so the user enters a start and end date, and i need to populate those months with either red amber or green (or 1, 2, 3)

then i need to look at each month (say June) and figure out its color..

i hope that makes more sense 🙂 look forward to your help 🙂 🙂

[hnguy71]

@ChadSmith22 ,

Is this what you're looking for?

I've overwritten the previous version. You can use the same link.

[ChadSmith22]

yes !!! and then i need to rate the actual "month" also if there are no dates in the month that are relevant (a custom calendar table??) for example if i need to show next 2 years.. and Jan-23-Dec-23 has no dates or scores, then its grey... thankyou so much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[hnguy71]

Hi @ChadSmith22 ,

Yes, you would need to supply a calendar with extended or future dates in order to make the correct evaluation.

I have updated the file to reflect your requirements.

[hnguy71]

@ChadSmith22 ,

hehehe, is this a job-related exercise? I believe it's to allow you to think it through and use your creativity to find a solution. What have you attempted already? And what hasn't worked?