Forum Discussion

homelander123's avatar
homelander123
Icon for Helper I rankHelper I
1 year ago
Solved

Dax not working

PreviousMonthCount = CALCULATE(SUM(Query1[NewCount] ),
FILTER(
    Query1,
    FORMAT(Query1[SetupDate], "yyyyMM") =
    FORMAT(EDATE(SELECTEDVALUE(Query1[SetupDate]),-1),"yyyyMM")

))


I am trying to use this query to show the previous month based on the month selected in the slicer but it is giving me no values in the table
"DAX " "DAX help" 'Need help'
"DAX help"
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi homelander123 

     

    Thank you very much Fowmy for your prompt reply.

     

    This is the principle of slicers. When you select a month in a slicer, it will only show data relevant to your selection.


    I recommend you create a date table as a slicer, but be careful not to create a relationship between the date table and the original table.

     

    Here's some dummy data

     

    "Query1"

     

    Create a Date table.

     

    Date = 
SELECTCOLUMNS(
    'Query1',
    "Date",
    'Query1'[SetupDate],
    "Year",
    'Query1'[SetupDate].[Year],
    "Month",
    'Query1'[SetupDate].[MonthNo]
)

     

     

    Select Year and Month from the Date table as slicers.

     

     

    Create a measure.

     

    PreviousMonthCount = 
VAR SelectYear = SELECTEDVALUE('Date'[Year])
VAR SelectMonth = SELECTEDVALUE('Date'[Month])
VAR PreviousMonthDate = EOMONTH(DATE(SelectYear, SelectMonth, 1), -1)
RETURN
CALCULATE(
    SUM(Query1[NewCount]),
    FILTER(
        ALL('Query1'),
        MONTH('Query1'[SetupDate]) = MONTH(PreviousMonthDate)
        &&
        YEAR('Query1'[SetupDate]) = YEAR(PreviousMonthDate)
        &&
        'Query1'[SetupDate] <= PreviousMonthDate
    )
)

     

    Here is the result.

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi homelander123 

     

    Thank you very much Fowmy for your prompt reply.

     

    This is the principle of slicers. When you select a month in a slicer, it will only show data relevant to your selection.


    I recommend you create a date table as a slicer, but be careful not to create a relationship between the date table and the original table.

     

    Here's some dummy data

     

    "Query1"

     

    Create a Date table.

     

    Date = 
SELECTCOLUMNS(
    'Query1',
    "Date",
    'Query1'[SetupDate],
    "Year",
    'Query1'[SetupDate].[Year],
    "Month",
    'Query1'[SetupDate].[MonthNo]
)

     

     

    Select Year and Month from the Date table as slicers.

     

     

    Create a measure.

     

    PreviousMonthCount = 
VAR SelectYear = SELECTEDVALUE('Date'[Year])
VAR SelectMonth = SELECTEDVALUE('Date'[Month])
VAR PreviousMonthDate = EOMONTH(DATE(SelectYear, SelectMonth, 1), -1)
RETURN
CALCULATE(
    SUM(Query1[NewCount]),
    FILTER(
        ALL('Query1'),
        MONTH('Query1'[SetupDate]) = MONTH(PreviousMonthDate)
        &&
        YEAR('Query1'[SetupDate]) = YEAR(PreviousMonthDate)
        &&
        'Query1'[SetupDate] <= PreviousMonthDate
    )
)

     

    Here is the result.

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    1 year ago

    homelander123 

    PreviousMonthCount = 
VAR SelectedMonth = MAX(Query1[SetupDate])
RETURN 
CALCULATE(
    SUM(Query1[NewCount]),
    Query1[SetupDate] >= EOMONTH(SelectedMonth, -2) + 1 && Query1[SetupDate] <= EOMONTH(SelectedMonth, -1)
)
    • Fowmy's avatar
      Fowmy
      Icon for Super User rankSuper User
      1 year ago

      homelander123 

      Try this:

      CurrentMonthLastYearCount = 
VAR SelectedMonth = MAX(Query1[SetupDate])
VAR LastYearSameMonthStart = EOMONTH(SelectedMonth, -12) + 1
VAR LastYearSameMonthEnd = EOMONTH(SelectedMonth, -12)

RETURN 
CALCULATE(
    SUM(Query1[NewCount]),
    Query1[SetupDate] >= LastYearSameMonthStart && Query1[SetupDate] <= LastYearSameMonthEnd
)