Distinct Value between date range

Question

Hey everybody...i need your help!I have a calculated table you can see below:&nbsp;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.&nbsp;At the end the table should look like this:&nbsp;OfferName&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DateStart&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DateEnd&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Quantity(correct)Office 365 Enterprise&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01.08.2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 07.08.2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1060Office 365 Enterprise&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 08.08.2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17.08.2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1065Office 365 Enterprise&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18.08.2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 27.08.2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1067Office 365 Enterprise&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 28.08.2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 28.08.2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1070Office 365 Enterprise&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 29.08.2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31.08.2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1075next product....&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ....&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ....&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ........&nbsp;Guys, I'm thankful for every help.&nbsp;&nbsp;&nbsp;

zubair_muhammad · Answer

Hi EMGI&nbsp;Please try this&nbsp;Go to Modelling Tab&gt;&gt;&gt;NEW TABLE&nbsp;New Table =
SUMMARIZE (
    TableName,
    TableName[OfferName],
    TableName[Quantity(correct)],
    "DateStart", CALCULATE ( FIRSTNONBLANK ( TableName[Date], 1 ) ),
    "DateEnd", CALCULATE ( LASTNONBLANK ( TableName[Date], 1 ) )
)

zubair_muhammad · Answer

EMGI&nbsp;

zubair_muhammad · Answer

EMGI&nbsp;pbix File attached here as well&nbsp;With your sample data

emgi · Answer

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:&nbsp;&nbsp;For example, the first line should looks like this (and the following just like that):&nbsp;Offer Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DateStart&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DateEnd&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Quantity(correct)Office 365 Enterprise E1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 09.08.2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31.08.2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 165...&nbsp;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 · Answer

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:&nbsp;OfferName&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OfferID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; StartDate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EndDate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Quantityazure mulitfactor &nbsp;&nbsp;&nbsp; 1234ab &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15.07.2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14.08.2017&nbsp;&nbsp;&nbsp;&nbsp; 1azure mulitfactor &nbsp;&nbsp;&nbsp; 1234ab &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15.08.2017&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14.09.2017&nbsp;&nbsp;&nbsp;&nbsp; 1Office 365&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5678cd &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15.07.2017 &nbsp; &nbsp;&nbsp; 14.08.2017 &nbsp; &nbsp; 160 &nbsp;&nbsp;Office 365&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5678cd &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15.08.2017 &nbsp; &nbsp;&nbsp; 13.09.2017 &nbsp; &nbsp; 165Office 365&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5678cd &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14.09.2017 &nbsp; &nbsp;&nbsp; 14.09.2017 &nbsp; &nbsp; 161...&nbsp;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)&nbsp;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?&nbsp;i hope you understand now better my problem and you or somebody else can help me with this issue.Thanks in advance...

Forum Discussion

Distinct Value between date range

7 Replies

Related content

count distinct

Index DISTINCT values

How to cound real distinct values?

Getting distinct values for multiple columns

Dax for sum of distinct values