Count of invoice last 12 months

[Zilliox]

Hello,

I am looking for having a filter where I can select if it is a monthly or sporadic customer. 

If we have invoiced the customer more than 5 times in the last 12 months than it is consider as a monthly customer otherwise it is sporadic customer.
I am adding my Power BI file here

Would really appraciate if someone can help me

Thanks

[AlB · Accepted solution]

@Zilliox 

What you ask for now is completely different from what you explained earlier.

Measure V2 = 
VAR minDate_ =
    DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ) //Takes last 12 months as of today
VAR months_ =
    CALCULATETABLE (
        DISTINCT ( Dates[Year Month] ),
        Dates[Date] > minDate_,
        Dates[Date] <= TODAY ()
    )
VAR numMonths_ =
    SUMX (
        months_,
         ( CALCULATE ( SUM ( Customers[Amount in currency with Current Xrate] ) ) > 0 ) * 1
    )
RETURN
    numMonths_

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Zilliox]

Thank you.

Sorry if I didn't express myself correctly

do you think you can help me with my other topic?

https://community.powerbi.com/t5/Desktop/Commission-Calculation-based-on-YTD/m-p/1502718#M621710

[AlB]

@Zilliox 

It does work. Here is the measure with your table names but exactly the same as before. It considers one billing day as one invoice. If you want to count it differently (i don't see any Invoice ID on the data) you can modify the measure

Measure = 
VAR minDate_ =
    DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ) //Takes last 12 months as of today
VAR numInvoices_ =
    CALCULATE ( DISTINCTCOUNT ( Customers[Billing Day] ), Dates[Date] > minDate_ )
RETURN
    IF ( numInvoices_ > 5, 1, 0 )

You need to place the measure in a visual with Customer (Gropued Global Logo Name?) in the rows.

The measure also takes the las 12 months from TODAY( ), since you did not specify further. Might want to change that as well.

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Zilliox]

Thanks for your help.

I don't have an invoice number. The measure have to count how much time we have an amount in the last 12 months. I

[Zilliox]

Hi,

The result is not correct.

This is what we have as data 

This is what I am looking for (You can see that on the last 12 months (yellow) how many time is the amount > 0)

[AlB]

Hi @Zilliox 

The link posted for the file requires a logon for download.

This measure will return a 1 if the condition is met, 0 otherwise. You can use it as filter for a visual. It expects to have the customer in the rows of the visual (or in the filter context)

Measure =
VAR minDate_ =
    DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ) //Takes last 12 months as of today
VAR numInvoices_ =
    CALCULATE ( DISTINCTCOUNT ( Table1[InvoiceID] ), DateT[Date] > minDate_ )
RETURN
    IF ( numInvoices_ > 5, 1, 0 )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Zilliox]

Hi,

I have tried your measure, but it doesn't work.
Can you please try if this link works? Click Here

[AlB]

@Zilliox 

Nope. It still asks for the logon. Perhaps you can upload it to tinyupload.com

What is it that doesn't work in the measure?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Zilliox]

Hello,

everything is blank when I do the measure

Herunder the link to tinyUpload

http://s000.tinyupload.com/index.php?file_id=92489439619460048040