Sameperiod last year counting the customers doing good or bad.

[Akhil_1411]

Hi Everyone,

I am trying to count the number of customers whose sameperiod last year is good or bad.

For example, Comparing %UCD Q1 2020 with the same period last year Q1 2019.  If it is -ve then my customer is doing good else bad. ( I am dealing with the unearned cash discount so it is viceversa).

After that i need to create the buckets like 0-5%, 5-10%, 10-15%, 20+%.

But here is the challenge i am facing, iam able to get the right result for same period last year. But, when i count based on that result number of customers getting the wrong answer.

Formulas:- Sameperiodlastyear =

Var A= CALCULATE([_% UCD],SAMEPERIODLASTYEAR(Merge1[Calendar Day].[Date]))

Var B = [_% UCD]

var c = (B-A)

return IFERROR(c,0)

Improved Customers:- getting wrong result

CALCULATE(DISTINCTCOUNT(Merge1[Sold_To_Ultimate_DUNS_Desc]),FILTER(Merge1,[Sameperiodlastyear]<=0.0))

Declined Customers:-getting wrong result

CALCULATE(DISTINCTCOUNT(Merge1[Sold_To_Ultimate_DUNS_Desc]),FILTER(Merge1,[Sameperiodlastyear]>=0.0))

After getting the above result need to create those buckets 0-5,5-10,15-20,20+, how to acheive this?

Thanks,

Akhil.

[Akhil_1411]

Can anyone help me how i can acheive this?

Thanks,

Akhil.

[Akhil_1411]

Hi All,

Sample power bi file.https://we.tl/t-afnRpAjgtS 

Thanks,

Akhil.

[V-lianl-msft]

Hi @Akhil_1411 ,

Try create mesure like this:

improved customers = CALCULATE(COUNTROWS(FILTER(VALUES(Orders[Customer ID]),[Difference CY - LY]>=0)))

declined customers = CALCULATE(COUNTROWS(FILTER(VALUES(Orders[Customer ID]),[Difference CY - LY]<0)))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Akhil_1411 , use a date table in time time intelligence

Var A= CALCULATE([_% UCD],SAMEPERIODLASTYEAR(Date[Date]))

Do not use .date unless date has a time stamp. For bucket refer

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Akhil_1411]

Hi @Amit,

I think this is not due to the date table creation. But thanks for sharing the info.

But still waiting for the concrete answer.

Thanks,

Akhil.