If date is between two dates return value

[Anonymous]

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 and Fiscal_Calendar.  In the Rolling_Calendar table, I would like to add a column that looks at the date column and compares it to the Fiscal_Calendar_StartFiscalMonth date and the Fiscal_Calendar_EndFiscalMonth date and return the FiscalMonth value.  

Ive tried using a formula like = IF(Rolling_Calendar_Date) = DatesBetween(Fiscal_Calendar_StartFiscalMonth, Fiscal_Calendar_EndFiscalMonth, Fiscal_Calendar_FiscalMonth, " ").

Any suggestions?

[v-angzheng-msft · Accepted solution]

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.

[Anonymous]

This is great! Thank you @v-angzheng-msft !

[Anonymous]

hello 

try writing DAX as 

FiscalMonth =
CALCULATE (
    VALUES ( FiscalMonth ),
    FILTER (
        Fiscal_Calendar,
        Rolling_Calendar_Date <= Fiscal_Calendar_StartFiscalMonth
            && Rolling_Calendar_Date >= Fiscal_Calendar_EndFiscalMonth
    )
)

[Anonymous]

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!

[edhans]

You can use LOOKUPVALUE() but that really would not be good practice here. You are treating DAX like these are Excel spreadsheets, and that isn't how it works. Your best bet would be to merge these two tables in Power Query, then get the relevant column out of it.

But I would ask what is your end goal? You need 1 good date table in the model, not 2 (as a rule) so tell us what you are trying to accomplish here and we can take a fresh look at it. We need data too. Cannot use images as source data.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Anonymous]

Yes I guess thats what I'm trying to do but so that I can create a relationship between the budget period and budget table that I have to create.  I tried to put all the information in one table but wasnt able to see how I would be able to create any relationships to other tables.  The budget table has three categories for each fiscal month.  I wasnt going to try and do all three so I was selecting 1 to start with. 

My goal is to create a budget category table that will link to a budget period table and calendar table Im currently working on.  These tables will eventually link to our order, customer and item tables. 

(The attached picture is the model Im trying to create based of Matt Allingtons Supercharge Power BI 3rd edition).

The calendar and budget tables will allow for us to slice by calendar month and by budget month.  It would also allow for us to include a KPI card to compare current sales to what the monthly budget is.