Count with only one row with specific value (start and end date problem)

[Anonymous]

Hello, 

I have a table which looks like this.

But I need powerBI to count with just one row for each country and I cant delete the other rows, because they all have different dates.

I got here by having end and start date for each country and I wanted users to be able to choose specific date and let powerBI to show the data that a the specific date falls into. I therefore created a list which entails those dates between, but then when I expand (for the filtering purpose) powerBI sums up the news values as well and the whole number doesnt add up.

Maybe there is some other way how to approach this whole start and end date problem, but I couldnt figure out anything.

Thank you

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could create a caldenar table as a slicer ,then create a measure to calculate the sum or discount.

1.create a calendar table.

Date = CALENDAR(MIN('Table1'[StartDate]),MAX('Table1'[EndDate]))

2.create a sum measure.

sum = CALCULATE(SUM([Value]),FILTER(ALLEXCEPT(Table1,Table1[ID]),[EndDate]>=MIN('Date'[Date])&&[StartDate]<=MAX('Date'[Date])))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mattww]

I'm not sure if I'm understanding your problem fully, but it seems like adding an index grouped by country might help, then you can filter on that to only include where the index = 1 to pull out one record per country. Similar to a ROW_NUMBER() with PARTITION in SQL. 

See this post which explains how you could go about adding this in Power Query

Create Row Number for Each Group in Power BI using Power Query - RADACAD

[Anonymous]

I managed to do the index, but my main problem is still not working. I will try to explain it.

I have a table of campaigns for each country with number of sent emails. These campaigns have a start and an end date. I want to be able to filter in report based on dates in that sense that powerBI shows all the data that falls into the data range. So far I have created column with lists with the dates between the start and end date. I also have created seperate date table (since I have multiple tables from other databases), which I use as a slicer in the report. So now I dont know how to connect those dates between the start and end date (so far they are in the list version and if I expand them, then the issue with more than just the original 1 value for each country appears). 
I tried to make relationship with the special date table and the column with lists from the campaign table, but that seems not to working.

[mattww]

I think I get what you mean, so for example you want a slicer where you select 30 September 2021 and that should be returning data for any campaign which was active at that point, for example a campaign with a Start Date 15 September & End Date 1 October

Let me know if I've misunderstood

[Anonymous]

Yes exactly that.

[mattww]

Got you, so I would have gone with your approach too, expanding your table to create a row for every day between the StartDate and EndDate, then using the new date column for your slicer

I would think you can use a MAX / MIN aggregation to then get values out like clicks/opens/etc, as long as you're still grouping by your campaign details, it should work fine, see simplified example below where the top table is the expanded source data, and the bottom table shows how you'd essentially deduplicate this using aggregation

If you just want to count the number of campaigns, use DISTINCTCOUNT on something like a campaign id column.

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

Matt