Max Month & Max Year Date Parameters in Paginated Reports

Power BI Paginated Reports
1

Hi,

i am working on a project where i am replicating some old, paginated reports into Power BI Report Builder.

Initially I had understood that the reports would need to call the specific month & year ordered and cancelled items for a subscription. unfortunately, i miss understood the requirements and my client wants to instead see all the records up to a specific date using the month and year parameters. I’m not sure how a parameter could be setup to get the max year and max month of the max date.

to add context, I created a custom column in my semantic model to grab whichever is the max of either dates to display it in another column called max date.

The main reason this is needed is that the Active and Cancelled Status of the subscription needs to be dynamic and changed based on if the subscription was cancelled on a specific month/year.

for example: Picking March, 2020 would result
Subscription 0, Ordered (2019-02-26), Cancelled (–), Status Active
Subscription 1, Ordered (2020-02-15), Cancelled (–), Status Active
Subscription 2, Ordered (2020-02-20), Cancelled (2020-03-21), Status Removed
Subscription 3, Ordered (2020-03-02), Cancelled (–), Status New

While picking February 2020 would result
Subscription 0, Ordered (2019-02-26), Cancelled (–), Status Active
Subscription 1, Ordered (2020-02-15), Cancelled (–), Status New

any help would be appreciated.

External Billing Summary.rdl (201.8 KB)

*** DELETE THIS MESSAGE IF YOU ARE SURE ALL YOUR DETAILS ARE COMPLETE OR IF THE ABOVE INFORMATION IS NOT APPLICABLE TO YOUR QUESTION.***

2

Hi @Ghislain.Comeau ,

While waiting for the community to review and respond to your issue, take advantage of Data Mentor . It offers a wealth of tools and resources that could provide immediate solutions and enhance your report-building efficiency.

Cheers,

Enterprise DNA Support Team

3

Answer:

Hi @Ghislain.Comeau,

If you have the month number (or name) and year number, you can create a date using “1” as the day number. Then you can use that date to derive the end of month date, and then use that as the parameter for filtering your results.

T-SQL has a handy EOMONTH() function, while SSRS/Paginated Reports requires a bit more calculating. While I couldn’t preview your report without the data source, I saw that you are using Month Name as the criterion along with the Year. The following code in the Expression designer will give you the End Of Month Date for any first day of the month assuming that the parameter label is a month name:

=DateAdd(“d”,-1,DateAdd(“M”,1,DateValue(Parameters!PrimaryMonthNum.Label & " 01, " & Parameters!PrimaryYearNum.Label)))

What it is doing is creating a string representation of the date (e.g. “March 01, 2024”), converting that to a DateValue, adding 1 month to the date, then subtracting 1 day to get the last day of the month.

Here is an example I created using one of my reports that had Month and Year criteria:

EDNA_EOM From Month-Year
EDNA_EOM From Month-Year897×187 10.7 KB

Now you can filter your results using <= End Of Month Date based on a selected month and year. NOTE: Be sure to handle the time portion of the value correctly. This example assumes your comparison dates have no time value or the time value on them is midnight.

You can find other date manipulation and generation functions under Common Functions > Date & Time in the Expression dialog.

John

4

Thanks jpratt, that actually makes a lot of sense! i will try it out and let you know if i hit any road blocks asap. my data source is the Dataverse for my semantic model so i can’t really play in SSRS for this. However, there is a couple DAX function i could use like ENDOFMONTH(‘Calendar’[Date]) in Power BI as well. I could setup something in the model.

5

Excellent! ENDOFMONTH() would be perfect in combination with using DATE() to create the date value for the 1st of the month from the individual parts. However you would need your Month and Year report criteria to use a number for each Parameter.Value so you can reference them directly in DATE().

FYI, the RDL format for Paginated Reports in the Power BI service is the same as for SSRS and Power BI Report Server. Microsoft is actually integrating SSRS into the Power BI service as “paginated reports” because they are trying to push everyone to the cloud.

6

i was very happy to see the new changes to paginated report in service this month. glad to see MS is giving Paginated report some love :slight_smile: