Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi There,
I am fairly new to power BI and trying to figure this out.. I want to create a date table that will have a series how collumns that show a "Yes" or "No" value based on specfied date ranges e.g 1 day ago, 2 days ago, last week, last month etc.
I have managed to get days working fine but I am struggling to get Last Month and Last week. Any idea on the formula I should use for these?
This is the formula I use to calculate yesterday:
Yesterday =
IF (
YEAR ( [Date] ) = YEAR ( TODAY () )
&& DAY ( [Date] ) = DAY ( TODAY () -1 ),
"Yes",
"No"
)
Any help would be much appricated! Thanks
Solved! Go to Solution.
Hi wupdedu,
To achieve your requirement, create two calculate columns using DAX formula like pattern below:
Last Month = IF(MONTH([Date]) = MONTH(EDATE(TODAY(), -1)), "Yes", "No") Last Week = VAR Current_Week = CALCULATE(MAX([Week No]), FILTER(Table1, Table1[Date] = TODAY())) RETURN IF([Week No] = Current_Week, "Yes", "No")
Regards,
Jimmy Tao
Hi wupdedu,
To achieve your requirement, create two calculate columns using DAX formula like pattern below:
Last Month = IF(MONTH([Date]) = MONTH(EDATE(TODAY(), -1)), "Yes", "No") Last Week = VAR Current_Week = CALCULATE(MAX([Week No]), FILTER(Table1, Table1[Date] = TODAY())) RETURN IF([Week No] = Current_Week, "Yes", "No")
Regards,
Jimmy Tao
Hi Jimmy,
Awesome thanks for that! The month one worked perfectly. The week one was still giving me current the current week.. I adjusted to this and seems to work giving me last week now:
Last Week = VAR Current_Week = CALCULATE(MAX([Week No]), FILTER(Table1, Table1[Date] = TODAY())) RETURN IF([Week No] +1 = Current_Week, "Yes", "No")
Thanks for your help!
See other post
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |