topic Re: Sales for auto select current month in Power BI in DAX Commands and Tips

Sales for auto select current month in Power BI

PreetiSahu321 — Tue, 22 Oct 2019 07:23:19 GMT

Hello Power BI Community,

Recently, I was working with Power BI with DAX. As per my requirement, I have an Orders list and this list is having some columns like Net value, Net price, etc where the Data type is the Whole number.

Here, I want to see the total net value as per the current month which will be auto-selected. Let's take an example to make it clear.

As per my requirement, I want to see the total net value of this current month (from 1st to 31st Oct). Similarly, it will show me the total net value of next month by auto-selected.

For this purpose, I have created 2 measures as:

1. To get the current month:
Current Month = max('Calendar'[year_month])

2. To get the total net value for the current month:

sales for current month = VAR Current_Month = [Current Month] RETURN CALCULATE (SUM (Orders[Net value]), FILTER (ALL ( 'Calendar'[year_month] ), 'Calendar'[year_month] = Current_Month ), Orders[Name1]="Orders")

After creating these two measures, when I am adding a Gauge chart from the Visualization, then I can not see my total net value for the current month. The chart is appearing like the below screenshot:

Can anyone suggest to me that what I can do anything else if my DAX is correct?

Thanks in Advance !!!

Re: Sales for auto select current month in Power BI

Nathaniel_C — Wed, 23 Oct 2019 00:52:24 GMT

Hi PreetiSahu321 ,

What you can do for testing is to put your measure on a card. That will tell you if it the measure, or the visual.
This may also have to do with the relationship.

Sales for current month =
VAR = [Current Month]
VAR _Calc =

    CALCULATE (
        SUM ( Orders[Net value] ),
        FILTER (
            ALL ( 'Calendar'[year_month] ),
            'Calendar'[year_month] = Current_Month
        ),
        Orders[Name1] = "Orders"
    )
return  _Calc

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: Sales for auto select current month in Power BI

PreetiSahu321 — Wed, 23 Oct 2019 06:46:10 GMT

Hi Nathaniel_C ,

I have added a Card from the Visualization. When I am adding this rule in the measure, it is not taking the variable name as only VAR and it is showing the error.

I think it is expecting the variable name should be either VAR.P, VAR.S, VARX.P or VARX.S as you can see in the below screenshot.

Also, After adding the rule as you sent, this below error is appearing. Here, the Current Month represents another measure that I have used to get the current month.

Please, can you suggest me if anything else I have to do?

Re: Sales for auto select current month in Power BI

Nathaniel_C — Wed, 23 Oct 2019 14:33:46 GMT

Hi PreetiSahu321 ,

Sorry, have to give a name with Var. Try var _curMonth = [Current Month]

Nathaniel

Re: Sales for auto select current month in Power BI

PreetiSahu321 — Thu, 24 Oct 2019 07:03:44 GMT

Hi Nathaniel_C ,

I have added the Variable name. Now no error is coming, but still the total Net value is not appearing in the Card as you can see in the below screenshot.

Please, can you suggest me anything else if you have any idea?