Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shado26
Helper III
Helper III

calculation number of day till today

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  

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Anonymous
Not applicable

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

View solution in original post

13 REPLIES 13
Sean
Community Champion
Community Champion

@shado26

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! Smiley Happy

@Sean

 

it calculate all day on quarter 91 

 

i need to calculate only till today which should be 73 day only 

Turnipface
Advocate I
Advocate I

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 

Anonymous
Not applicable

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 

Anonymous
Not applicable

Result is 73:

 

Days quarter.PNG

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 

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.