Display dynamic message each week from list in a card visualization

[JDAnalytics]

My company has a list of 15 messages that we display to our team members, one each week. I'm trying to find a way to display each message in a Card visualization in order each week, and repeat it automatically.

Is there a way to do this with DAX or Power Query?

For example:

Week 1: "Message 1"

Week 2: "Message 2"

...

Week 15: "Message 15"

Week 16: "Message 1"

[Anonymous · Accepted solution]

Hi @JDAnalytics ,

I have created a simple sample, please refer to it to see if it helps you.

Create a calendar table.

date table = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))

Then create columns.

weeknum = WEEKNUM('date table'[Date],1)

yushu = var _mod=MOD('date table'[weeknum],15)
return
IF(_mod=0,_mod+15,_mod)

Then create a measure to get the message.

Measure_message= CALCULATE(MAX('Table'[message]),FILTER(ALL('date table'),'date table'[yushu]=SELECTEDVALUE('Table'[weekno.])))

Finally create a column.

put into card = IF('date table'[Date]=TODAY(),("Week"&'date table'[weeknum]&":"&[Measure_message]),BLANK())

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards
Community Support Team _ Polly

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

[vanessafvg]

you would need to create a table to precalculate which week it is in and what message should be passed.

for example you  have your date table, which then links to a messagedate table and in that you have the message and date you need to calculate for that date.  What is the rule by which the week is decided

Or you can also potentially build a rule into power bi.    It depends on how you derive when the week must start.    So you will need a start date and then you could also create a index in a table that starts on a start date, and depending on which week it is afterthe start date, it could calculate via a table or switch() statement.

[JDAnalytics]

Yeah, the current way I have it is a date table with id's 1-15 that repeat, and show the current week's message in the card, I was looking for a more dynamic way to do it rather than manually assigning the 1-15 to each week. 

Could you give an example of how to calculate via a table or switch statement?