Forum Discussion
Time analysis in Paginated reports
- Anonymous5 years ago
Hi wfreeman
I build a sample for you, you may refer to the data model and code in this sample.
Calendar table:
Calendar = VAR _T1 = ADDCOLUMNS ( CALENDARAUTO (), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "YearMonth", YEAR ( [Date] ) * 100 + MONTH ( [Date] ) ) VAR _T2 = ADDCOLUMNS ( _T1, "RANK", RANKX ( _T1, [YearMonth],, ASC, DENSE ) ) RETURN _T2Relate Date column with Date column in your Fact table.
Measures:
Last Month Value = CALCULATE(SUM('Table'[Value]),PREVIOUSMONTH('Calendar'[Date]))Change Percentage = VAR _Change = SUM('Table'[Value]) - [Last Month Value] RETURN DIVIDE(_Change,[Last Month Value])Result is as below.
You may refer to this blog to build measures in Paginated reports.
For reference:
Create a paginated report based on a Power BI shared dataset
How to use measures in power bi report builder which was created in Power Bi dataset
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi wfreeman
I build a sample for you, you may refer to the data model and code in this sample.
Calendar table:
Calendar =
VAR _T1 =
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"YearMonth",
YEAR ( [Date] ) * 100
+ MONTH ( [Date] )
)
VAR _T2 =
ADDCOLUMNS ( _T1, "RANK", RANKX ( _T1, [YearMonth],, ASC, DENSE ) )
RETURN
_T2
Relate Date column with Date column in your Fact table.
Measures:
Last Month Value =
CALCULATE(SUM('Table'[Value]),PREVIOUSMONTH('Calendar'[Date]))Change Percentage =
VAR _Change = SUM('Table'[Value]) - [Last Month Value]
RETURN
DIVIDE(_Change,[Last Month Value])
Result is as below.
You may refer to this blog to build measures in Paginated reports.
For reference:
Create a paginated report based on a Power BI shared dataset
How to use measures in power bi report builder which was created in Power Bi dataset
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.