DAX formula help needed to look at two fields and populate certain dates based on values.

[krichmond]

I am extremely new to Power BI and even newer to DAX so please try to take that into consideration when posting help. Thank you so much in advance. Learning as I go but this one has completely confused me.

I need help creating a DAX formula that does the following. The new field name should be "Media Due Date". It looks long but all this is really doing is looking at a field called "Media Type" and if that field shows "Digital" a certain set of dates populate and if that field shows "Direct Mail" another set of dates populate.

The "Start Date" sits in a dimension / table called "dimstartdate". The "Media Type" field sits in a dimension / table called "mv_perfex". I don't really care where the new field is created as long as it works as expected.

Link to sample data extract: https://www.dropbox.com/scl/fi/m0whlzi461ollu3odzoc6/Sample-Data-For-Community-Post-11-04-2022.xlsx?...

1. If the "Start Date" field is in January 2023 and the "Media Type" field is "Digital" than populate "12/14/2022".
2. If the "Start Date" field is in February 2023 and the "Media Type" field is "Digital" than populate "1/19/2023".
3. If the "Start Date" field is in March 2023 and the "Media Type" field is "Digital" than populate "2/15/2023".
4. If the "Start Date" field is in April 2023 and the "Media Type" field is "Digital" than populate "3/23/2023".
5. If the "Start Date" field is in May 2023 and the "Media Type" field is "Digital" than populate "4/20/2023".
6. If the "Start Date" field is in June 2023 and the "Media Type" field is "Digital" than populate "5/17/2023".
7. If the "Start Date" field is in July 2023 and the "Media Type" field is "Digital" than populate "6/21/2023".
8. If the "Start Date" field is in August 2023 and the "Media Type" field is "Digital" than populate "7/20/2023".
9. If the "Start Date" field is in September 2023 and the "Media Type" field is "Digital" than populate "8/23/2023".
10. If the "Start Date" field is in October 2023 and the "Media Type" field is "Digital" than populate "9/20/2023".
11. If the "Start Date" field is in November 2023 and the "Media Type" field is "Digital" than populate "10/18/2023".
12. If the "Start Date" field is in December 2023 and the "Media Type" field is "Digital" than populate "11/21/2023".
13. If the "Start Date" field is in January 2023 and the "Media Type" field is "Direct Mail" than populate "12/7/2022".
14. If the "Start Date" field is in February 2023 and the "Media Type" field is "Direct Mail" than populate "12/7/2022".
15. If the "Start Date" field is in March 2023 and the "Media Type" field is "Direct Mail" than populate "1/6/2023".
16. If the "Start Date" field is in April 2023 and the "Media Type" field is "Direct Mail" than populate "2/13/2023".
17. If the "Start Date" field is in May 2023 and the "Media Type" field is "Direct Mail" than populate "3/14/2023".
18. If the "Start Date" field is in June 2023 and the "Media Type" field is "Direct Mail" than populate "4/10/2023".
19. If the "Start Date" field is in July 2023 and the "Media Type" field is "Direct Mail" than populate "5/11/2023".
20. If the "Start Date" field is in August 2023 and the "Media Type" field is "Direct Mail" than populate "6/9/2023".
21. If the "Start Date" field is in September 2023 and the "Media Type" field is "Direct Mail" than populate "7/10/2023".
22. If the "Start Date" field is in October 2023 and the "Media Type" field is "Direct Mail" than populate "8/11/2023".
23. If the "Start Date" field is in November 2023 and the "Media Type" field is "Direct Mail" than populate "9/11/2023".
24. If the "Start Date" field is in December 2023 and the "Media Type" field is "Direct Mail" than populate "10/4/2023".

I should note that I have a DAX formula that works simply using the "Start Date" field. However, it is the addition of the "Media Type" field that is causing me not to know how to do this. I attached a screenshot of the DAX formula that currently works using just the "Start Date" field in case you want to reference it.

DAX Formula That Works Using Just "Start Date"

[Greg_Deckler]

@krichmond Just add " && [Media Type] = "Digital" right before the first comma in each row?

[krichmond]

@Greg_Deckler - I tried this based on your post, but for some reason it isn't working. As you can see from the screenshot, the fields associated with the "dimstartdate" dimension / table pick up just fine. However, when I type "mv_perfex" to pick the "Media Type" field it doesn't show that it is connecting to it like the other fields do from the "dimstartdate" dimension / table. Any idea what I am doing wrong? Probably user error. 🙂

[Greg_Deckler · Accepted solution]

@krichmond This is a column, correct? If you column is in a different table you probably need to use RELATED('mv_perfex'[mediatype])

[krichmond]

@Greg_Deckler - Would I need to put that on the front end of each row? And would a sample of something that works be what is below?

RELATED('mv_perfex'[mediatype]) = Digital && dimstartdate[monthnumber] = 1 && dimstartdate[Yearnumber] = 2023, "12/7/2022",

Just a reminder, I am extremely new to Power BI and even newer to DAX.