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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
RanHo
Helper V
Helper V

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.