Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi folks,
I'm struggling with converting YTD financial data to periodic (MTD) that comes from different year-end regimes.
Imported | Imported | Imported | Imported | DAX |
Account | Date | YTD Value | DataType | MTD Value |
AB | Dec 20 | 160 | CY | 160 |
AB | Jan 21 | 50 | CY | 50 |
AB | Feb 21 | 90 | CY | 40 |
AB | Mar 21 | 110 | CY | 20 |
AB | Apr 21 | 50 | FY | 50 |
Pivot Total | 320 |
In the table above, CY stands for Calendar Year while Fiscal Year now ends in March. Is it possible using a DAX measure to convert YTD to MTD resulting in the above MTD values including correct Pivot Totals?
Best regards,
Chris
MTD for a completed month is the same as "value for that month". Use that field for your fiscal year time intelligence function.
Hi, my solution yielded the following dax with a little help from power query M:
Value MTD CY:=CALCULATE([YTD Value];[DataType]="CY") -
CALCULATE([YTD Value];[DataType]="CY"; DATEADD([Date];-1;MONTH);MONTH[Date]<12;[SubtractYTD])
Value MTD FY:=CALCULATE([YTD Value];[DataType]="FY") -
CALCULATE([YTD Value];[DataType]="FY"; DATEADD([Date];-1;MONTH);MONTH[Date]<>3;[SubtractYTD])
Value MTD:= Value MTD CY + Value MTD FY
SubtractYTD compares Date with MaxDate by DateType in calculated column due to underlying data characteristics.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |