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