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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.