Last 2 weeks sales in a direct query

[khaledhikmat]

Hi, I have data that looks smething like this in a direct query PowerBI:

How can I get the revenue for the latest n weeks please? Thanks.

Regards,

Khaled

[ankitpatira]

@khaledhikmat In power bi desktop go to query editor (Edit Queries) -> and for the week column click on the dropdown icon and select only past two weeks. 

[khaledhikmat]

Sorry...I think I must have not have been clear! I meant i wanted a DAX expression like TOPn or RANK to dynamically pick up the latest two n weeks so I can show in a visualtion the revenue for the last n weeks. I am new to PowerBI and DAX ...I appreciate any pointer.

Thanks.

[ankitpatira]

@khaledhikmat What I would do is create a calculated column as shown below and then use that column as a slicer on the report. Unfortunately I don't think there is a way currently to make top N value dynamic but you can using below DAX code it for as many weeks as you want and it may meet your requirements.

[khaledhikmat]

Ah...ok...I see. It does make sense I guess. So now it looks like this:

But I have 2 questions:

How can I sort the weeks based on a dimension that is not included in the visual i.e. week number?

How can I set the slicer to default to top weeks?

Thank you so much for your help. I appreciate it.

Regards

[v-yulgu-msft]

Hi @khaledhikmat

Based on my research, a chart visual can only be sorted based on the column you added to Axis or Value. That is to say, it is not allowed to sort the weeks based on a dimension that is not included in the visual.

Besides, it's not very clear about how you want to sort the chart. If possible, would you please share some more specific sample data regarding to this chart and some detailed description about your except result.

Best regards,
Yuliana Gu

[khaledhikmat]

Hi, thank you for your reply.

Basically my data contains week number (i.e. 34, 25, 36, 37, etc), year, week name (i.e. 22 Oct 2016) and revenue. I have a bar chart that shows revenue for weeks. For clarity, I needed to include the week name as the axis so users would know what they are looking for.

As you pointed out, my sort options are revenue or week name, but both options are not good for me as I need to show the weeks in their natural chronological order order such as 15 Oct 2016, 22 Oct 2016, 29 Oct 2016, etc. If I can sort by week number, they will come out in order.

I hope this is clear. What do you think? Thank you so much for your help.

Regards

[v-yulgu-msft]

Hi @khaledhikmat,

I have tested your scenario again, but unfortunately I didn't find any workaround to achieve that. It is actually not allowed to sort the chart based on a dimension that is not included in the visual. 

Thanks,
Yuliana Gu

[khaledhikmat]

For the slicer, I set a page level filter and it seems to work. Regarding the sorting question, I appreciate an answer. Thanks.