Count Number of Days in current fiscal month based on the current date in Fiscal Month

[romoguy15]

Hello, 

I have an accounting calendar that we use in our organization. I would like to create a Dax Measure that dynamically counts the number of days in the current fiscal month based on the current date. 

Example:

If todays date is 11/9/20, that means the current fiscal month is Nov. There are 28 Days in this fiscal month based on our accounting calendar. November fiscal month starts on 10/31/20. So counting from 10/31/20 to 11/9/20, that would be 10 days. I would like a dax measure to do just that. I would like it to be dynamic as the current date is changing.

I have a sample data with a link to my OneDrive. I hope this would help provide a solution

https://1drv.ms/u/s!AqID1H0nHPOzg3fgc5fLMvrXcfYl?e=Zq8ypm

I could not figure out how to poste it in this message. 

[jdbuchanan71 · Accepted solution]

@romoguy15 Give this a try.

Days = 
VAR _FiscalPeriod = SELECTEDVALUE('Calendar'[Period & Year])
VAR _Today = TODAY()
RETURN
CALCULATE(
    COUNTROWS('Calendar'),
    'Calendar'[Period & Year] = _FiscalPeriod,
    'Calendar'[Date] <= _Today
)

For your second quesiton in the file, of total days in fiscal period it would be.

Days in period for date = 
VAR _FiscalPeriod = SELECTEDVALUE('Calendar'[Period & Year])
VAR _Today = TODAY()
RETURN
CALCULATE(
    MAX('Calendar'[Days In Fiscal Month]),
    ALL('Calendar'),
    'Calendar'[Period & Year] = _FiscalPeriod
)

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

calculate(countrows(calendar),datesbetween(calendar[date],eomonth(min(calendar[date]),-1),today()))

Hope this helps.

[jdbuchanan71 · Accepted solution]

@romoguy15 Give this a try.

Days = 
VAR _FiscalPeriod = SELECTEDVALUE('Calendar'[Period & Year])
VAR _Today = TODAY()
RETURN
CALCULATE(
    COUNTROWS('Calendar'),
    'Calendar'[Period & Year] = _FiscalPeriod,
    'Calendar'[Date] <= _Today
)

For your second quesiton in the file, of total days in fiscal period it would be.

Days in period for date = 
VAR _FiscalPeriod = SELECTEDVALUE('Calendar'[Period & Year])
VAR _Today = TODAY()
RETURN
CALCULATE(
    MAX('Calendar'[Days In Fiscal Month]),
    ALL('Calendar'),
    'Calendar'[Period & Year] = _FiscalPeriod
)

[romoguy15]

Thank you, this is exactly what I needed