Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Why dax return first month as April and last month as September values??

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

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

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

    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)

    pbix
    TestBI.pbix   39 KB 1 version


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

    Posted 28 days ago

    @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??

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

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

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