cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shado26 Member
Member

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

Accepted Solutions
SPG Member
Member

Re: calculation number of day till today

Result is 73:

 

Days quarter.PNG

SPG Member
Member

Re: calculation number of day till today

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
11 REPLIES 11
Turnipface Regular Visitor
Regular Visitor

Re: calculation number of day till today

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

 

shado26 Member
Member

Re: calculation number of day till today

no this cant work for me as we need to have number of day to add on some formula to calculated on daily bases 

Super User
Super User

Re: calculation number of day till today

@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

SPG Member
Member

Re: calculation number of day till today

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 ","

shado26 Member
Member

Re: calculation number of day till today

@Sean

 

it calculate all day on quarter 91 

 

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

shado26 Member
Member

Re: calculation number of day till today

@SPG

 

it calculate all day on quarter 91 

 

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

SPG Member
Member

Re: calculation number of day till today

Result is 73:

 

Days quarter.PNG

Highlighted
shado26 Member
Member

Re: calculation number of day till today

Hi @SPG

 

can previous formula can apply to count per month & per year ?? please advice 

SPG Member
Member

Re: calculation number of day till today

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