Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
)
Hi,
Try this measure
calculate(countrows(calendar),datesbetween(calendar[date],eomonth(min(calendar[date]),-1),today()))
Hope this helps.
Hi,
Try this measure
calculate(countrows(calendar),datesbetween(calendar[date],eomonth(min(calendar[date]),-1),today()))
Hope this helps.
@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
)
Thank you, this is exactly what I needed
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |