Calculate quarter-to-date values with starting month from February in Dax

[zx00289]

Hi,

I have a set of data that would like to calculate the demand in quater-to-date.

My financial year starts from February hence my quarter-to-date value for Q1 would be 26591+39276+24091=89958, Q2 would be 97159 and etc. 

I have created a data table as below:

Date =

VAR FirstDayOfWeek = 0 -- 0 = Sunday, 1 = Monday, ...

RETURN

GENERATE (

CALENDARAUTO (),

VAR Yr =

YEAR ( [Date] ) -- Year Number

VAR Mn =

MONTH ( [Date] ) -- Month Number (1-12)

VAR Qr =

QUARTER ( [Date] ) -- Quarter Number (1-4)

VAR MnQ = Mn - 3 * ( Qr - 1 ) -- Month in Quarter (1-3)

VAR Wd =

WEEKDAY ( [Date], 1 ) - 1 -- Weekday Number (0 = Sunday, 1 = Monday, ...)

RETURN

ROW (

"Year", DATE ( Yr, 12, 31 ),

"Year Quarter", FORMAT ( [Date], "\QQ-YYYY" ),

"Year Quarter Date", EOMONTH ( [Date], 3 - MnQ ),

"Quarter", FORMAT ( [Date], "\QQ" ),

"Year Month", EOMONTH ( [Date], 0 ),

"Month", DATE ( 2000, MONTH ( [Date] ), 1 ),

"Fiscal Month No",

IF ( MONTH ( [Date] ) >= 2, MONTH ( [Date] ) - 1, MONTH ( [Date] ) + 11 ),

"Fiscal Quarter",

"Q" & IF (MONTH( [Date] ) = 1, QUARTER( [Date]) + 3 , IF(MONTH([Date]) IN {4, 7, 10}, QUARTER([Date])-1, QUARTER([Date]))),

"Fiscal Year",

IF ( MONTH ( [Date] ) >= 2, DATE ( ( Yr + 1 ), 12, 31 ), DATE ( Yr, 12, 31 ) )

)

)

How should I get the demand value in quater-to-date using Dax?

Thanks.

[v-yiruan-msft · Accepted solution]

Hi @zx00289 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Best Regards