Forum Discussion
If date is between two dates return value
This looks like a common question on here but I cant seem to find a good solution for my situation. I'm new to PBI and am trying to understand how DAX works. I have two tables: Rolling_Calendar...
Hi, Anonymous
Try to create a measure like below :
__FiscalMonth = VAR _a = SELECTEDVALUE ( 'Rolling_Calendar'[Date] ) RETURN CALCULATE ( MAX ( 'Fiscal_Calendar'[FiscalMonth] ), FILTER ( ALL ( 'Fiscal_Calendar' ), 'Fiscal_Calendar'[EndFiscalMonth] >= _a && _a >= 'Fiscal_Calendar'[StartFiscalMonth] ) )Result:
Please refer to the attachment below for details
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hello
try writing DAX as
FiscalMonth =
CALCULATE (
VALUES ( FiscalMonth ),
FILTER (
Fiscal_Calendar,
Rolling_Calendar_Date <= Fiscal_Calendar_StartFiscalMonth
&& Rolling_Calendar_Date >= Fiscal_Calendar_EndFiscalMonth
)
)
CALCULATE (
VALUES ( FiscalMonth ),
FILTER (
Fiscal_Calendar,
Rolling_Calendar_Date <= Fiscal_Calendar_StartFiscalMonth
&& Rolling_Calendar_Date >= Fiscal_Calendar_EndFiscalMonth
)
)
Thanks for your suggestion. I tried using the formula but the column isnt returning any values. Ill try some variations of the formula to see if theres something Im missing!