• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Monthly Slicer Problem in YTD Chart

Cristof

New Member
Hi,
I have a probably simple DAX (Power BI) problem.
I have a simple matrix with sales per month and a DateTable.
I would like to show on one page a slicer with the months (1 to 12; from DateTable), a table showing the selected month sum of sales and the YTD some of sales, and a simple bar chart showing the sum of sales per month up to the month selected in the slicer.
My problem is for example, when I choose 4 (April) in the slicer, the bar chart only shows the April sum of sales, not the sales for January to March.
I am not sure, where the problem is? I suppose I need another measure to use in the bar chart, but I dont know how to "overwrite" the slicer selection but still keep to the maximum date from the slicer...
Can anyone help?
 

GraH - Guido

Well-Known Member
Isn't there a timeline slicer in powerBI?
If I'm not mistaken, in Excel you can select a month in the slicer, and if you press SHIFT, you select a range (e.g. Slice on March, but using shift it will select January-March). With CTRL you can select one by one. Might be PowerBI has the same behavior.
If that is not working, you may need to make a version of your measures that filters the value for all months YTM.
 

Chihiro

Excel Ninja
My problem is for example, when I choose 4 (April) in the slicer, the bar chart only shows the April sum of sales, not the sales for January to March.
I'm guessing that you are using straight SUM() for your measure and using slicer selection.

You can construct measure to check if slicer has one value selected. Then construct your measure based off of the selected value.

However, as GraH mentioned, using timeline slicer is much easier (it's downloadable as custom visual). Though I personally don't like how much real-estate is takes up.
 

Cristof

New Member
Hi,
thanks for the Response.
If I try the timeline slicer in power bi, the effect, if I select months Jan-Apr. the bar Chart will Show the correct graph, but then, the table will Show the same values in the columns for monthly sales and YTD sales. And excat this is, what I dont want to have.
The bar Chart should Show the monthly YTD values, the table should Show in one column the selected month value and in the other column the YTD sales value.
I hope this additional explaination will help to Show my Problem.
 

Chihiro

Excel Ninja
Without sample not really. As DAX is highly contextualized in how it's evaluated.

I'd recommend mocking up sample of how you want it to look like in Excel (or even hand drawn). Then uploading sample data set. It will make it so much easier for us to help you.
 
Top