Forum Discussion

Caitlin_Knox's avatar
Caitlin_Knox
Advocate III
10 years ago

Snapshot Reporting

I have a need to create a sort of static report. The Customer wants to see a snapshot of the entire base of contract values up until the end of the month for a 13-month view.

My initial thought is to have an IF statement like this

=IF(Table_owssvr[Created]<DATEVALUE("05/31/2015"),[Commissionable amount],BLANK()) and so on for each month following up until the current month. So the next one for June would include Mays total as well. The only problem is that I'm not sure how to produce a visualization for this, or if this is even the best way to achieve this. I'd like to overlay the monthly commissionable amount on top of this bar graph too as a line graph with the points showing the sum total for that month alone, without the previous months.

This is what I have so far as far as a visualization..

Need Help

3 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    10 years ago

    Hi Caitlin_Knox,

     

    BAsed on my understanding, users want to see contract values from the start month to current month. If current month is June, then want to see values untill to June month. But if the current month is July, they want to see values untill to July month, right?

     

    If that is a case, you can create a query parameter which has a list of month value (1,2,3,4,5,....), then add a filter condition to filter the date column based on this query parameter. When user runs the report, it will prompt him to select the month number to filter data. For more information, please refer to this article: Deep Dive into Query Parameters and Power BI Templates.

     

    To add draw a line for the current month instead of previous months, you can create a calculated column like this:

     

    line_value = IF([Month Number]=MONTH(TODAY()),[Commissionable amount],BLANK())

     

    Then add a Line and Clustered column chart, place date in Shared axis and line_value as Line Values like below:

     

     

    If you have any question, please feel free to ask.

     

    Best Regards,
    Qiuyun Yu

    • Caitlin_Knox's avatar
      Caitlin_Knox
      Advocate III
      10 years ago

      They want to see contract values of EVERYTHING active up until that month- so this could potentially include contract values from years prior.

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Community Support
        10 years ago

        Hi Caitlin_Knox,

         

        Do you mean users want to see all records from the previous years to last month? If that is a case, you can create a calculated column use the expression below:

         

        =IF(Table_owssvr[Created]<DATEVALUE(Year(TODAY())& "-" &MONTH(TODAY())& "-"&"01"),[Commissionable amount],BLANK())

         

        Then place this column value as Column Values in Line and a Clustered column chart.

         

        Best Regards,
        Qiuyun Yu