Forum Discussion

EMGI's avatar
EMGI
Frequent Visitor
8 years ago

Distinct Value between date range

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 "ord...
Need Help
EMGI's avatar
EMGI
Frequent Visitor
8 years ago

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's avatar
EMGI
Frequent Visitor
8 years ago

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's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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.