How to get value and differentiate into 2 category / Measure

[RanHo]

 
 Good day Guys!

   I just need help on this measures, I have a problem and sample here in image:

as you can see , I need to get the vaue of products that falls between those 2 categories (0-90 days and 90 days above). I need to filter it first with  Month and Year , how can I get those in measures?  And additional is it possible to that if the count falls to 0-90Days then can I add another column and return it as "WITHIN DATE" and if it's in the 90 days above return as " EXPIRED" ?

  Any help or advice will appreciate. Thanks!



~RAN

[rubayatyasmin · Accepted solution]

Hi, @RanHo 

Follow these steps

Step 1: Filter by Month and Year

If you have a date column in your dataset, you can create a slicer for month and year in Power BI. To do this:

1. Go to the "Fields" pane.
2. Drag the date field into the "Values" box of a slicer visual.
3. In the slicer visual, click on the down arrow in the "Values" box and select "Month" or "Year". This will give you a slicer that you can use to filter your data by month or year.

Step 2: Differentiate between two categories (0-90 days and 90 days above)

Create a new column that calculates the difference between the current date and the product date. You can use the DATEDIFF function for this:

Days Difference = DATEDIFF(YourTable[Product Date], TODAY(), DAY)

Step 3: Classify as "WITHIN DATE" or "EXPIRED"

Create another new column that uses the "Days Difference" column to determine whether a product falls within the "0-90 days" category or the "90 days above" category:

Category = IF(YourTable[Days Difference] <= 90, "WITHIN DATE", "EXPIRED")

Hope this helps. 

[rubayatyasmin · Accepted solution]

Hi, @RanHo 

Follow these steps

Step 1: Filter by Month and Year

If you have a date column in your dataset, you can create a slicer for month and year in Power BI. To do this:

1. Go to the "Fields" pane.
2. Drag the date field into the "Values" box of a slicer visual.
3. In the slicer visual, click on the down arrow in the "Values" box and select "Month" or "Year". This will give you a slicer that you can use to filter your data by month or year.

Step 2: Differentiate between two categories (0-90 days and 90 days above)

Create a new column that calculates the difference between the current date and the product date. You can use the DATEDIFF function for this:

Days Difference = DATEDIFF(YourTable[Product Date], TODAY(), DAY)

Step 3: Classify as "WITHIN DATE" or "EXPIRED"

Create another new column that uses the "Days Difference" column to determine whether a product falls within the "0-90 days" category or the "90 days above" category:

Category = IF(YourTable[Days Difference] <= 90, "WITHIN DATE", "EXPIRED")

Hope this helps. 

[RanHo]

@rubayatyasmin thanks! It worked! Accepted as Solution

But I have another aditional column called COLLECTED/PAID TABLE : all paid products is here/counted on this table. So
 If the product is <=90days , and it's in the PAID table then result = WITHIN DATE
 If the product is >90days , and it's in the PAID table then result = WITHIN DATE
 If the product is <=90days , and it's NOT in the PAID table then result = BLANK
 If the product is >90days , and it's NOT in the PAID table then result = EXPIRED

Is this possible? 

Many thanks!!

[rubayatyasmin · Accepted solution]

@RanHo Good to know it worked. 

Assuming that you have a unique identifier for each product that is shared between YourDataTable and CollectedPaidTable (e.g., ProductID), you can create a relationship between these two tables based on this identifier.

try this

Date_Label =
VAR IsPaid =
NOT(ISBLANK(
RELATED(CollectedPaidTable.ProductID)
))
RETURN
SWITCH(
TRUE(),
AND(DATEDIFF(YourDataTable.ProductDate, TODAY(), DAY) <= 90, IsPaid), "WITHIN DATE",
AND(DATEDIFF(YourDataTable.ProductDate, TODAY(), DAY) > 90, IsPaid), "WITHIN DATE",
AND(DATEDIFF(YourDataTable.ProductDate, TODAY(), DAY) <= 90, NOT(IsPaid)), BLANK(),
AND(DATEDIFF(YourDataTable.ProductDate, TODAY(), DAY) > 90, NOT(IsPaid)), "EXPIRED",
BLANK() // default
)

Please replace YourDataTable, ProductDate, CollectedPaidTable, and ProductID with your actual table names and column names.

[RanHo]

@rubayatyasmin Thanks a lot! It's actually worked.