Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Raj Mh]

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
——————————

[Vilmar Santos]

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
——————————
——————————————-

[Raj Mh]

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
——————————
——————————————-

[Rd 3nidad]

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
——————————
——————————————-

[Raj Mh]

thanks it work as expected

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

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

[Kaz Shakir]

,
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
——————————
——————————————-

[Raj Mh]

Hello Kaz,

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

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

[Adam Artur Boltryk]

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
——————————
——————————————-

[Tomas Torp]

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
——————————
——————————————-