Why dax return first month as April and last month as September values??

Why dax return first month as April and last month as September values??Follow
Raj Mh
Raj MhOct 28, 2022 10:14 AM
Hello, I have a simple table called date table and it returns or calculate values for month of April …
1. Why dax return first month as April and last month as September values??

Raj Mh
Posted Oct 28, 2022 10:14 AM
Hello,

I have a simple table called date table and it returns or calculate values for month of April instead start of the month in data table as Nov.
Also, for last month it return September instead of Oct.

Is anyone had similar kind of issue?

DAX:
>> start_dt =
CALCULATE (
[TotalSales],
FILTER ( date, date[MMYY].[Month] = MIN ( ( date[MMYY].[Month] ) ) )
)

>>end_dt =
CALCULATE (
[Totalsales],
FILTER ( date, date[MMYY].[Month] = MAX ( ( date[MMYY].[Month] ) ) )
)

Thanks for supporting
Raj

——————————
Raj Mh
Manager
——————————

2. RE: Why dax return first month as April and last month as September values??

Vilmar Santos
Posted Oct 28, 2022 10:30 PM
Olá

Na expressão está o nome do mês, altere para o número do mês.
Se não der certo, envia o pbix, pode ser com dados fictícios.

——————————
Vilmar Santos
——————————

3. RE: Why dax return first month as April and last month as September values??

Raj Mh
Posted Oct 31, 2022 07:30 AM
Thanks Vilmar,

Its not the case as data is correct. In my case there are product categories which may occurred as per sales transaction in any month and DAX shall pick the correct start and end date for that product category. To summarize below is the sample table and it shall pick date and Sales value like:

Product Name Start Month Sales End Month Sales
Product100 Jan-21 100 Dec-21 100
Product200 Jan-21 200 Dec-21 200
Product300 Mar-21 300 Dec-21 300

Sample table :

Month Sales Product Name
Jan-21 100 Product100
Jan-21 200 Product200
Feb-21 100 Product100
Feb-21 200 Product200
Mar-21 100 Product100
Mar-21 200 Product200
Mar-21 300 Product300
Apr-21 100 Product100
Apr-21 200 Product200
Apr-21 300 Product300
May-21 100 Product100
May-21 200 Product200
Jun-21 100 Product100
Jun-21 200 Product200
Jul-21 100 Product100
Jul-21 200 Product200
Aug-21 100 Product100
Aug-21 200 Product200
Sep-21 100 Product100
Sep-21 200 Product200
Oct-21 100 Product100
Oct-21 200 Product200
Nov-21 100 Product100
Nov-21 200 Product200
Dec-21 100 Product100
Dec-21 200 Product200
Dec-21 300 Product300
Thanks for supporting

——————————
Raj Mh
Manager
——————————

4. RE: Why dax return first month as April and last month as September values??

Rd 3nidad
Posted Nov 01, 2022 01:20 PM | view attached
Hi, I’m not sure what is wrong with your data but if you are just looking for the expected output as you mentioned, you can use helper columns in the dataset such as rank ascending and / or descending. Consider the sample report attached.

——————————
Rd 3nidad
Senior Finance Analyst
——————————

Attachment(s)

pbixTestBI.pbix

5. RE: Why dax return first month as April and last month as September values??

Raj Mh
Posted Nov 04, 2022 09:27 AM
@Rd 3nidad thanks it work as expected

Thanks to others as well who all supported in this thread!​

——————————
Raj Mh
Manager
——————————

6. RE: Why dax return first month as April and last month as September values??

Kaz Shakir
Posted Oct 31, 2022 12:32 PM
@Raj Mh,
In your formulas you are taking the MIN and MAX of the field called date[MMYY].[Month]. Can you tell us the data type for that field? Is it a number, or is it text, or is it a date? In other words would it have a value of 1, 2, 3 for Jan, Feb, Mar? or would it have the values of “Jan”, “Feb”, “Mar”?

Kaz.​

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

7. RE: Why dax return first month as April and last month as September values??

Raj Mh
Posted Nov 01, 2022 01:28 AM
Hello Kaz,

Data type for Month column is ‘Date’ and its not value like 1-21 instead its Jan-21

——————————
Raj Mh
Manager
——————————

8. RE: Why dax return first month as April and last month as September values??

Adam Artur Boltryk
Posted Nov 01, 2022 03:29 PM
Hi Raj,
Hard to say without full view of your model.
It can be:

1. Filter context from page.
2. Year can be set on Fiscal Year.
3. as TODAY is 1st day of the new month, data and model are not submitted/refreshed yet.
4. a few more reasons.

Regards,

——————————
Adam Artur Boltryk
Business Analyst
——————————

9. RE: Why dax return first month as April and last month as September values??

Bronze Contributor
Tomas Torp
Posted Nov 01, 2022 04:10 PM
Hi Raj,

As long as you do not turn off auto date/time in the settings, a hidden date table will be generated for each date column in the model, so while date[MMYY] might be of data type date, the column date[MMYY].[month] in the hidden date table is of data type text. Therefore, April is the minimum month and September is the maximum month.

One way to fix this is to use the MONTH function instead, but keep in mind that this will only make sense as long as you look at one year at a time:

>> start_dt =
CALCULATE (
[TotalSales],
FILTER ( date, MONTH ( date[MMYY] ) = MONTH ( MIN ( date[MMYY] ) ) )
)​

>> end_dt =
CALCULATE (
[TotalSales],
FILTER ( date, MONTH ( date[MMYY] ) = MONTH ( MAX ( date[MMYY] ) ) )
)​

——————————
Tomas
——————————