Forum Discussion

wfreeman's avatar
wfreeman
Frequent Visitor
5 years ago
Solved

Time analysis in Paginated reports

I am looking for a little help creating a paginated report.  I am doing analyitics on police activity, looking to show percentage of change between a selected month and the month prior.  I would like one column to represent current value, and have second column represent month prior and ideally a third column showing the percentage of change.   Anyone have any suggestions??  Thank you!!

 

Bill

Need Help
  • Anonymous's avatar
    Anonymous
    5 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
    _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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 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
    _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.

  • wfreeman's avatar
    wfreeman
    Frequent Visitor
    5 years ago

    Thank you for the reply.  My data model does contain a date table already, do I need to still add the date table in your example? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi wfreeman 

      Here I build a date table is to show you that you need a continuous date table to use time intelligence function like "PREVIOUMONTH" in your report. If you already have a related date table with continuous date, you can use this one directly. If the dates in your date table aren't continuous, build a date table by my way.

      For reference: Get the DAX query for the 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.

  • wfreeman's avatar
    wfreeman
    Frequent Visitor
    5 years ago

    Thank you again for the help!  It did work for getting the data that I wanted!  It worked with just using some tables from my SQL database, but if I try to use my live connection SQL Analysis I get an error trying to connect to the power bi report using paginated report builder.  Once I have published the report to the service, i use a gateway to connect to our data.  This is error I am getting if you have any solutions.....