DATEADD combined with filter function

[nmarti13]

Hi there,

I was wondering if anyone could help me. I'm rather new to power bi.

Essentially, I have a dynamic chart which shows how much advertising has been booked for the months ahead. The filters set in place include title group, format, on sale date, date booked and budget date.

Budget date is the revenue target for that month, date booked is when the date the advertising was booked and the on sale date is when it goes on the shelves and is publsihed. 

The  format filter includes digital and print. Print is prepared and set to publish 2 months in advance and digital one month. 

I have a visualisation which shows the revenue target based on budget date, and i would like to show that against the actual figure which is based on the date booked e.g. August and the on sale date e.g. November when it is actually published to see how much revenue is booked ahead. However, I need a formula which will basically sum the net revenue for print as two months behind and digital one month behind. Maybe it is quite simple but I have no idea.

After that on another page I would like to show a new target which would be what finished on for the month divided by what was booked for that month on the last day of the previous month.

Thanks,

I would be so grateful for your help! 

[v-jiascu-msft]

Hi @nmarti13,

Can you share a dummy sample?

It seems you need to consider different dates in different columns at the same time. Maybe you can create many relationships. Only one is active. Then you can use dax/userelationship-function-dax in the formula.

Best Regards,

Dale

[nmarti13]

Hi Dale,

Thanks so much for your response. Please see the dashboard belolw and the mapping I have done. I have map only one date box that i've only mapped to the budget dates and on sale date. I don't know if I need to edit this in some way, maybe that is the issue? 

Thanks,

Natalie 

[v-jiascu-msft]

Hi Natalie,

I'm afraid we can't see much from the images. Can you share the pbix file? You can make a DUMMY sample.

Best Regards,

Dale

[nmarti13]

Hi Dale,

Sorry how do I do that as some of the data is confidential?

Thanks,

Natalie 

[v-jiascu-msft]

Hi Natalie,

Create a new dummy one which can help us write DAX or troubleshoot.

Best Regards,

Dale

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.