Forum Discussion

minghui97's avatar
minghui97
New Member
5 years ago

Monthly report

Hi All,

 

I am new to powerbi,thanks for your patient to answer my question!

my company database(sql server)has much data contains information from 2005, it is unwise to import all data.

Question 1:I want a dashboard that shows monthly up to date data.

For example,today is July 10,my dashboard will show the data from July 1 to July 10. Next month it will show data in Aug.How can I do that?

 

question2: I need a monthly dashboard,this month it shows information for last month.next month shows information for this month. How to do it? 

thanks!

Need Help

3 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    minghui97  There are many different ways to filter the data for the report. In your case, I would recommend maybe pulling in 1 year of data, but based on your description you need at least 2 months. 

    One way to do this is using Power Query column filters on your date columns, you could use the Date Filters > Date is In Previous... for the most flexibility. Just make sure it's a relative filter, so last three months OR this month for example (note the screenshot below hasn't had the 'Or' selected yet but this will need to be done for it to work).

     

     

    Then use a date table and filter your report and visualizations by month:

    https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi minghui97 ,

     

    You can also use it with incremental refresh to reduce the amount of data in the report. Please refer to Incremental refresh for datasets.

     

    For question 2, you can create a seperate calendar table, then writing dax like

    Measure = CALCULATE(SUM('Table'[Value]),FILTER('Table',MONTH([Date])=MONTH(MAX('Table 2'[Date]))-1))

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    minghui97 How are you doing with this report? 

     

    You do not need to create any special measures to get a monthly report, just use a Date table and relate it to your SQL data. Then you can filter the report using 'Relative date slicer' https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range so that every time you open the report it always shows the previous month. 

     

    To do that, choose Last 1 Month (Calendar) from the slicer as per the link I shared above. 

     

    Let us know if you have any other questions, otherwise please mark the solution that helped you so others can find it easily.

     

    Cheers!