How to improve user experience when using date parameters in the paginated report - Power BI

#POWERBI #PAGINATEDREPORTS #DATEPARAMETERS #POWERBIDATASETS
In this video, we will improve the user experience of using paginated reports by changing how Date parameters look when you use Power BI datasets as a data source, We will go thru each step to change parameters and the underline DAX query which will improve the user experience of running paginated reports.

[@erinann7833]

THANK YOU, THANK YOU, THANK YOU!!! Yours seems to be the only video I could find showing how to do this properly. You are amazing and a life saver.

[@PowerBIHowTo]

Glad I could help! Thanks for the feedback. Much appreciated. Best regards!!

[@АлексейМиронов-н6ч]

Man, You are THE BEST!!!!

[@PowerBIHowTo]

Thanks you so much. I'm not but I will take it :) Appreciate the feedback. Regards!

[@travelkare]

Love the passion and consistency through which you keep coming with not so regular powerbi stuff

[@PowerBIHowTo]

Thanks @Arpit for your feedback. I will do my best to keep doing these video and keep sharing. Help spread the word so that others can take advantage of it. Thank you! If you have a topic you would like to cover, please let me know.

[@TheRitutas]

Thank you for such a clear explanation. It was life saver for me :D

[@PowerBIHowTo]

Glad it helped! Much appreciated!

[@ryryz4659]

exactly the problem I faced. Thank you tons

[@PowerBIHowTo]

Glad it was helpful. Appreciate the feedback. Thanks!

[@shaharyaramjad4416]

Thank you so much! Worked like magic

[@PowerBIHowTo]

Thanks for the feedback. Glad to hear it was helpful. Best regards!

[@AndresLopez-ee5su]

Thanks much for this, this worked for me. Unfortunately, when I add another parameter after changing the DAX code, it reverts back to original code. However, I got around that by adding report parameters instead. Keep the videos coming, thanks again.

[@PowerBIHowTo]

Glad it helped. Thanks for the feedback 🙏

[@devopsworld8357]

Great explanation

[@PowerBIHowTo]

Thanks for the feedback! 🙏

[@radhae8486]

Tq .. nice explanation 👌

[@PowerBIHowTo]

Welcome 😊 Thanks for the feedback!

[@kariannekies3248]

Thank you so much, sir! :)

[@PowerBIHowTo]

Most welcome! Thank you 🙏

[@VictorHugo-bd3bf]

That's so cool! Thanks for sharing. Is it possible to create a report that shows overdue invoices as of a specific date? For example, could I calculate all overdue invoices based on the date the user selects and break them down into measures like 1-30 days overdue, 31-60 days overdue, by customer dimension?

[@PowerBIHowTo]

Yes you can do this. There are many articles on how you can do this? Search for "power bi dynamic segmentation " and you will find many results. Great question though. Let me know if you are not able to figure it out. Thanks!

How did you set up your Calendar datasource in where the dates in your calendar dimension are able to return the relevant timeframe data from the dimGeography datasource?

[@PowerBIHowTo]

Hi there, sorry not sure about your question. Could you please provide more details? Thanks!

[@Montreal_powerbi_connect]

Nice!!!

[@PowerBIHowTo]

Thanks Atiry 🙏

[@emilymalkin270]

Hello, and thank you for the video. Would you create a video about how to run paginated report from inside power bi - with optional parameters, multi select parameters and with shared datasets as data source (so Dax is used to build queries, not SQL like in most tutorials). Ideally, the users should pass power bi slicer values to parameters, while paginated report parameters stay hidden. Ty Also, I wish to use query parameters rather than report filters because report filters make paginated report run very slowly. However, using query builder to berate query parameters that are multi select and optional using datasets is not working well at all.

[@PowerBIHowTo]

Hi there, Thanks for the feedback and the suggestion for another video. I have something similar here on embedding paginated report in Power BI. Let me know what you think about this and after that if you tell me what is missing I will do my best to get another video out. Thank you!

[@yuntianpu4463]

Thank you so much, it worked well when only date parameter is applied, but it doesn't work after I add a few more text parameter and gave me the below error. Query (1, 114) DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[@PowerBIHowTo]

Glad it helped! That seems to be for some another issue, seems like you are something where data type of parameter and column is not matching. It will be very hard to tell without looking at the DAX query.

[@yuntianpu4463]

Hi Below is my dax Query DEFINE VAR vFromLedgerTransactionsAccountingDate1 = DATEVALUE(@FromLedgerTransactionsAccountingDate) VAR vToLedgerTransactionsAccountingDate1= DATEVALUE(@ToLedgerTransactionsAccountingDate) EVALUATE SUMMARIZECOLUMNS('Ledger Transactions'[Accounting Date], 'Ledger Transactions'[JournalNumber], 'Ledger Transactions'[Entity], 'Ledger Transactions'[department], 'Ledger Transactions'[costcenter], 'Ledger Transactions'[location], 'Ledger Transactions'[VOUCHER], 'Ledger Transactions'[AMOUNT], 'VendorTrans'[vendnum], 'VendorTrans'[vendname], 'Ledger Transactions'[Ledger], 'CustomerTrans'[Custnum], 'Ledger Transactions'[Description], 'Ledger Groups'[Ledger Name], 'Ledger Transactions'[Rownum], 'CustomerTrans'[Custname], 'Companies (Entities)'[Company Full Name], FILTER(VALUES('Ledger Transactions'[Accounting Date]), ( 'Ledger Transactions'[Accounting Date] >= vFromLedgerTransactionsAccountingDate1) && ('Ledger Transactions'[Accounting Date]

[@tamerajohnson1730]

Hello I loved your video--I am currently using your suggestions when creating Date Parameters in Paginated Reports. However, I have a situation where I have over 500 members and they have a population. I have a column for Population Rank--What I want to do is to do a similar type parameter where I choose those members who are pop rank of 5 above and 5 below the selected member population rank. So say, for example, that I have a member who is population rank of 10--I want to show all members who are population rank of 5-15 dynamically. This will eventually be a report that will be ran in a data flow and each report be put into a pdf file, so I need to have it where there is no interaction with the slicers. I have everything running so far, I am just not able to get the 5 above and 5 below working. Can you please point me in the right direction? Thank you

[@PowerBIHowTo]

Hi there, Thanks for the feedback and glad you find this video useful. Regarding your question, let me clarify few things, your table has 3 columns (maybe more) but I'm using 3 that are important, and member, population and rank, correct? Q1 - rank is a column or a measure? Q2 - you want a numeric parameter starting from 1 to the maximum rank number, let's assume maximum is 50. so parameter will list 1 to 50 values, correct? Q3 - Use select a number from this parameter, assuming 19 is selected. you want to show customer with rank between 14 to 24 (19+/- 5), correct? Will it include selected number as well? Q4 - What if use selects 1, the range will be 1 to 6 (or 2 to 6), depending if we are keeping selected number or not? Thanks!

[@shanhuahuang3063]

your solution works based on 2 items: your calender date is a date value while mine is text. convert @parameter to date in query

[@PowerBIHowTo]

Yes, that is correct. it is using date value from the calendar table.

[@shanhuahuang3063]

i got an error saying parameter is not declared. do you know how to get around it? i used same expression as you

[@PowerBIHowTo]

It is very hard to tell why it is not working. If you can share the query and the little details about your data model then I will able to tell why it is not working.

[@ryryz4659]

I also want to know how to set the date parameter to run recent one month data automatically

[@PowerBIHowTo]

Sorry for the late reply. Could you please elaborate bit more. Thanks!

[@kavitaahuja856]

Can we add min date from a field to set calendar default date through expression. PLease help

[@PowerBIHowTo]

Yes, you can surely do that. Write an expression that return the MINIMUM date value and use that for the parameter.

[@oscariglesias1268]

Hola, al pasar el informe paginado en Power BI, sólo me permite ponerle un parámetro de fecha ¿cómo se podría poner los dos parámetros, el desde y el hasta? Gracias Hello, when passing the paginated report in Power BI, it only allows me to put a date parameter, how could I put the two parameters, from and to? Thank you

[@PowerBIHowTo]

Just to confirm, you are embedding paginated report in Power BI desktop using visual, correct?

[@oscariglesias1268]

@@PowerBIHowTo Correcto , puedo "activar" los dos párametro, desde y hasta, pero sólo me permite en uno poner la columna de fecha. Correct, I can "activate" the two parameters, from and to, but it only allows me to put the date column in one

[@oscariglesias1268]

FromCALENDARIOFECHA.......Off..... FECHA....2018-09-01 00:00:00.0 ToCALENDARIOFECHA....Off...Seleccionar campo relacionado...ya no puedo poner Columan fecha de tabla calendario FromCALENDARIODATE.......Off..... DATE....2018-09-01 00:00:00.0 ToCALENDARDATE....Off...Select related field...I can no longer put Columan table calendar date

[@PowerBIHowTo]

@@oscariglesias1268 I already did a video on this, check this link kzbin.info/www/bejne/n4POh5SsrKmfpc0 Let me know if you have any further questions. Cheers!!

[@oscariglesias1268]

Solved, thanks... you are a great professional. Thanks for sharing