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
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

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 ) )
)
    • 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...