Forum Discussion
Helper VHow 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:
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
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:
- Go to the "Fields" pane.
- Drag the date field into the "Values" box of a slicer visual.
- 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 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.
4 Replies
rubayatyasminCommunity Champion
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:
- Go to the "Fields" pane.
- Drag the date field into the "Values" box of a slicer visual.
- 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
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.
