Distinct Value between date range

[EMGI]

Hey everybody...i need your help!

I have a calculated table you can see below:

In the first column you can see one product (but got many different ones; this ist just an example), the "order date" and the number of licences for each day.

A customer can change his number of licences daily, the red marked fields show you when the customer changes his number.

At the end the table should look like this:

OfferName                     DateStart              DateEnd           Quantity(correct)

Office 365 Enterprise      01.08.2017           07.08.2017          1060

Office 365 Enterprise      08.08.2017           17.08.2017          1065

Office 365 Enterprise      18.08.2017            27.08.2017         1067

Office 365 Enterprise      28.08.2017           28.08.2017          1070

Office 365 Enterprise      29.08.2017           31.08.2017          1075

next product....               ....                          ....                         ....

....

Guys, I'm thankful for every help.

[Zubair_Muhammad]

Hi @EMGI

Please try this

Go to Modelling Tab>>>NEW TABLE

New Table =
SUMMARIZE (
    TableName,
    TableName[OfferName],
    TableName[Quantity(correct)],
    "DateStart", CALCULATE ( FIRSTNONBLANK ( TableName[Date], 1 ) ),
    "DateEnd", CALCULATE ( LASTNONBLANK ( TableName[Date], 1 ) )
)

[Zubair_Muhammad]

@EMGI

[Zubair_Muhammad]

@EMGI

pbix File attached here as well

With your sample data

[EMGI]

@Zubair_Muhammadsorry for my late reply...

Thanks a lot for this solution, looks great. But i have a problem to filter this table for each customer.

To get the licence numbers for each customer i add the customer to your "new table". Thats easy...

But now i have the problem that the "ChargeEndDate" could be in a different moth. I only want to get the range of date for the active month in the filter:

 

For example, the first line should looks like this (and the following just like that):

Offer Name                         DateStart           DateEnd             Quantity(correct)

Office 365 Enterprise E1      09.08.2017        31.08.2017        165

...

If i have this view, i want to calculate (this table has a relationship to price table which contains the price for each day) the price for each period. How can i solve this?

Thanks a lot...

[EMGI]

To provide for a better understanding i try to explain a little bit more in detail:

i import a table which contains the datas for each customer. In this file the data are just like this:

OfferName              OfferID          StartDate         EndDate         Quantity

azure mulitfactor     1234ab          15.07.2017      14.08.2017     1

azure mulitfactor     1234ab          15.08.2017      14.09.2017     1

Office 365               5678cd          15.07.2017      14.08.2017     160   

Office 365               5678cd          15.08.2017      13.09.2017     165

Office 365               5678cd          14.09.2017      14.09.2017     161

...

in a calculated table i get the daily numbers of licence for a daily report.

The customer could change his numbers every day, if he wants. (see the last line in the table above)

If i use your solution @Zubair_Muhammad, i didn't see the product "azure.." in my table.

I think, the problems are the formula "FIRSTNONBLANK" or rather "FIRSTLASTBLANK", or am i wrong?

i hope you understand now better my problem and you or somebody else can help me with this issue.

Thanks in advance...

[EMGI]

Hello again,

has nobody an idea or an easy step how to move forward on this issue?

Any help would be appreciated.

Thanks again...

[v-jiascu-msft]

Hi @EMGI,

Can you share your file? (Please be aware of your privacy) If you want to make the End Date dynamic, maybe you need a measure. That depends on your data model. 

One more thing, maybe using the Start Date in a slicer is a bad idea. It will filter down (hide) the unqualified records. The Start date of Azure is 15.07 that is smaller than the left value of the slicer. That's why you can't see Azure in your table.

Best Regards,

Dale.