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

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'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.