Forum Discussion

Poojara_D12's avatar
Poojara_D12
Super User
1 year ago

DAX KPI Issue

Hi,

I'm creating a measure in which I want to count monthly average of sales (With and without tax), as per Financial year. If the user select previous FY it should divide the amount by 12 if current than amount divide by (current month -1).

I have written a dax for the same in DAX query window, where it is showing me the expected output for current and previous year.

 

I use the same code and create a new measure and it's not showing the correct output as shown in DAX query window.

There's no page or report level filters in the dashboard. Don't know why it's not showing the correct result.

The correct result is the 1st image in DAX query view.

 

Can anyone please help??

 

FreemanZ , SamWiseOwl , Dangar332 , DataNinja777 , Ritaf1983 , Kedar_Pande 

 

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

3 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    Poojara_D12 

    Please share a simplified version of your PBIX file (in English) without sensitive data. You can upload it to a public cloud service like OneDrive, Google Drive, or Dropbox and share the link. This will help in understanding your data structure and the issue, allowing for more precise guidance.

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    Hi, Wrap your existing DAX logic in a calculate  function within the measure to explicitly control the context:

    Monthly Average Without Tax =
VAR StartDate =
CALCULATE(
MIN('Calendar'[Date]),
'Calendar'[FiscalYear] = SelectedFiscalYear
)
VAR EndDate =
CALCULATE(
MAX('Calendar'[Date]),
'Calendar'[FiscalYear] = SelectedFiscalYear
)
VAR IsFiscalYearComplete =
MAX('Purchase Analysis'[Posting_Date]) <= EndDate
VAR SalesInFiscalYear =
CALCULATE(
SUM('Purchase Analysis'[Purchase Amount]),
'Calendar'[FiscalYear] = SelectedFiscalYear,
'Purchase Analysis'[Type] = "Item"
)
VAR MonthsInPeriod =
IF(
IsFiscalYearComplete,
12,
DATEDIFF(StartDate, TODAY(), MONTH) - 1
)
RETURN
DIVIDE(SalesInFiscalYear, MonthsInPeriod, 0)



    1. Explicit Filter Context: The calculate function ensures the measure operates in the same evaluation context as in the DAX query window.

    2. Dynamic Context Management: It avoids unexpected filter overrides by visuals or the model relationships.
  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    1 year ago

    Hi, Poojara_D12 

    Can you furthur clarify your requirment ?, it's hard to predict what you looking for from above description.