Column for number of days with sales (between two dates from different tables)

[Anonymous]

Hi,

I have tried to find a solution in previous posts here, but I am still struggling to find something that works, although I feel it shouldn't be so complicated. Still fairly new to Power BI though.

I have a table with sales that include amount and payment date, as well as a key to a separate campaign table, which includes campaign start date among other things. For our campaigns, we want to to report on sum sales per day in the first 30 days of a campaign (as an example). The start date of the campaign is booking day 1, the next day booking day 2 etc. I want to visualize this in a table that shows sum sales per booking day, but excluding days without any payments (usually weekends, but could be other days as well). So if March 1st was the campaign start date and there were sales this day, this would be booking day 1. If the next day of sales was March 4th, then that would be booking day 2. 

I've managed to set up a column in my sales table that shows a count of days between the campaign start date and the payment date on each individual payment using the DATEDIFF function, which I can then use in visualisations. But I don't understand how I can exclude days without payment from the count of dates, which I guess is something I can't do within the sales table itself (as no payment means no row to exclude). I have tried many different things, e.g. using different functions, using a date table connected to the sales table where I have added a column of "has payment yes/no" for each date, adding a freestanding calender table ++ But can't seem to figure out. Any suggestions?

Thanks in advance,

Emma

[amitchandak]

@Anonymous ,Can you share sample data and sample output.

Also, you refer to my blog, if that can help

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

[Anonymous]

Not sure if this is sufficient, but here it a very simplified example (note that the payment dates skip dates, but the booking day then only counts the dates with payments):


My actual report has thousands of payments and also many different campaigns. An end user might for example wish to compare the results over the first 30 booking days on all campaigns with the same start year. 

[Anonymous]

Here's an example of the result I get today just using a datediff function based on the campaign start date and payment date.

[v-chuncz-msft]

@Anonymous 

You may try using RANKX.

[Anonymous]

Thanks for your suggestion, looking into it now. How would you use RANKX in the example I provided above? 

The aim I guess would be to rank the dates between campaign start date and payment date, while excluding dates without payment from the ranking? And can this ranking then be created as column in the sales table?