Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BobKoenen
Helper IV
Helper IV

Count of items with lastdate

Hi dear Power bi experts,

 

I have the following Datatabel

 

CountryCustomerItemPurchase date
USAABicycle1-12-2010
USAABicycle1-1-2000
USAACar20-11-2018
USADBicycle5-4-2013
NetherlandsBBicycle2-5-2015
NetherlandsCCar2-8-2010
NethelandsCBicycle14-4-2018

 

Now I want the Customers which last bicycle purchase was >5j. 
I have done this via a measure lastdate = lastdate([purchases]purchasedate) and put visual level filters on the table 

Item = Bicycle

Lastdate = <1-6-2020. 

It works and gives me this table and is Exactly what i Want

CustomerItemLastdate
ABicycle1-12-2010
BBicycle2-5-2015
DBicycle5-4-2013

 

However I do not really like how I have done this because it is not Dynamic because i need to alter the date from time to time. Also i would like to create a count per country which should give me

CountryCount of customers purchase bike >5j
USA2
Netherlands1

 

I am not able to do that because I keep ending up in with a result were all bikes are counted and not only the last purchashed. 

Do you have any good ideas?

1 ACCEPTED SOLUTION

Hi @BobKoenen ,

You can create 2 measures as below to achieve it:

Measure = 
VAR _tab =
    SUMMARIZE (
        'Purchases',
        'Purchases'[Country],
        'Purchases'[Customer],
        'Purchases'[Item],
        "lastPurchaseDate", MAX ( 'Purchases'[Purchase date] )
    )
VAR _lastPdate =
    MAXX ( _tab, [lastPurchaseDate] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Purchases'[Customer] ),
        FILTER (
            'Purchases',
            'Purchases'[Country] = MAX ( 'Purchases'[Country] )
                && 'Purchases'[Item] = SELECTEDVALUE ( 'Purchases'[Item], "Bicycle" )
                && DATEDIFF ( _lastPdate, TODAY (), YEAR ) >= 5
        )
    )
Measure 2 = 
SUMX (
    VALUES ( 'Purchases'[Country] ),
    SUMX ( VALUES ( Purchases[Item] ), [Measure] )
)

Count of items with lastdate.JPG

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
AllisonKennedy
Super User
Super User

Do you have a DimDate table? https://allisonkennedycv.blogspot.com/2020/04/dimdate-what-why-and-how.html

Use the DimDate and time intelligence such as DateAdd to make this dynamic.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Yes I do have including current year ofsset. But i only want the date of the last bike and not all bikes purchased before <5j I do not know how to solve this issue using the date table. 

Hi @BobKoenen ,

You can create 2 measures as below to achieve it:

Measure = 
VAR _tab =
    SUMMARIZE (
        'Purchases',
        'Purchases'[Country],
        'Purchases'[Customer],
        'Purchases'[Item],
        "lastPurchaseDate", MAX ( 'Purchases'[Purchase date] )
    )
VAR _lastPdate =
    MAXX ( _tab, [lastPurchaseDate] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Purchases'[Customer] ),
        FILTER (
            'Purchases',
            'Purchases'[Country] = MAX ( 'Purchases'[Country] )
                && 'Purchases'[Item] = SELECTEDVALUE ( 'Purchases'[Item], "Bicycle" )
                && DATEDIFF ( _lastPdate, TODAY (), YEAR ) >= 5
        )
    )
Measure 2 = 
SUMX (
    VALUES ( 'Purchases'[Country] ),
    SUMX ( VALUES ( Purchases[Item] ), [Measure] )
)

Count of items with lastdate.JPG

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Try using something like DATEDIFF() to get the time between TODAY() and PurchaseDate. Set your filter to 5j for the DATEDIFF() value.

For your count, you will want to do something like: COUNTROWS(FILTER(Customer, DATEDIFF_value >5))

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Yes I have a date table including current year offset but do not know how to set this problem up

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.