appreciate you help here
i need formula to calculate number of day till today in Quarter
Ex Quarter start 1st of July till 29 of Sept so total number of day is 91
i need to count how many day from start of Quarter till today let say today 11-9-2018 so i should have result with 73 day
Solved! Go to Solution.
There may be better ways to do it, but I didn't have much time now and I wanted to keep the "step by step" logic to make you understand it better.
Days Since First Day of Fiscal Month = VAR currentYear = YEAR(TODAY()) VAR currentMonth = MONTH(TODAY()) VAR currentDay = DAY(TODAY()) VAR firstDayOfFiscalMonth = 26 VAR fiscalMonth = IF( currentDay >= firstDayOfFiscalMonth; currentMonth; MONTH(EOMONTH(TODAY(); -1)) ) VAR fiscalYear = IF( currentMonth = 1 && currentDay < firstDayOfFiscalMonth; currentYear - 1; currentYear ) RETURN DATEDIFF(DATE(fiscalYear; fiscalMonth; firstDayOfFiscalMonth); TODAY(); DAY) + 1
Here are 2 options:
1) DAX Column
Day in Quarter = 'Table'[Date] - STARTOFQUARTER ( 'Table'[Date] ) + 1
2) M Column
= Number.From ( [Date] - Date.StartOfQuarter ( [Date] ) ) + 1
Try this measure. I've tried to write it "step by step" so you can easly understand it:
Days Since First Day of Quarter = VAR currentQuarter = ROUNDUP(MONTH(TODAY()) / 3; 0) VAR firstMonthInCurrentQuarter = currentQuarter * 3 - 2 VAR firstDayOfCurrentQuarter = DATE(YEAR(TODAY()); firstMonthInCurrentQuarter; 1) RETURN DATEDIFF(firstDayOfCurrentQuarter; TODAY(); DAY) + 1
Depending on your language you may have to replace the ";" by ","
It is a little bit easier but you can do it the same way
Days Since First Day of Month = VAR firstDayOfCurrentMonth = DATE(YEAR(TODAY()); MONTH(TODAY()); 1) RETURN DATEDIFF(firstDayOfCurrentMonth; TODAY(); DAY) + 1
Days Since First Day of Year = VAR firstDayOfCurrentYear = DATE(YEAR(TODAY()); 1; 1) RETURN DATEDIFF(firstDayOfCurrentYear; TODAY(); DAY) + 1