cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors