March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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
Solved! Go to 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:
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.
Proud to be a Super User!
@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.
Proud to be a 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:
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.
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.
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
87 | |
84 | |
76 | |
49 |
User | Count |
---|---|
163 | |
148 | |
103 | |
74 | |
55 |