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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
RanHo
Helper IV
Helper IV

How to get value and differentiate into 2 category / Measure

 
 Good day Guys!

   I just need help on this measures, I have a problem and sample here in image:
SAMPLE.png
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

2 ACCEPTED SOLUTIONS
rubayatyasmin
Super User
Super User

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_0-1689517080227.png

 


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

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

 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

4 REPLIES 4
rubayatyasmin
Super User
Super User

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_0-1689517080227.png

 


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


@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!!



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

 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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