cancel
Showing results for
Did you mean:
New Member

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

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.

1 ACCEPTED SOLUTION
Community Support

Hi @zx00289 ,

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

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support

Hi @zx00289 ,

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

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors