Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
HI All
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
HTH!
it calculate all day on quarter 91
i need to calculate only till today which should be 73 day only
Would it work if you create a Card visualization (displaying a number) for measurement with the formula = DISTINCTCOUNT(Table[Dates])?
This will then change depending on what you filter/select in the vizualizations.
Otherwise, you might be able to go for DATESYTD, DATESQTD or DATESINPERIOD
no this cant work for me as we need to have number of day to add on some formula to calculated on daily bases
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 ","
This doesnt work for me
@Anonymous
it calculate all day on quarter 91
i need to calculate only till today which should be 73 day only
Result is 73:
How can we make this dynamic to change based on the month selected
Hi @Anonymous
can previous formula can apply to count per month & per year ?? please advice
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
Thank you @Anonymous this work fine
but can we make as fiscal month start as our month start on 26-8 so i should have 22 day not 17 day
Days Since First Day of Month = VAR firstDayOfCurrentMonth = DATE(YEAR(TODAY()); MONTH(TODAY()); 1) RETURN DATEDIFF(firstDayOfCurrentMonth; TODAY(); DAY) + 1
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |