DAX KPI Issue

[Poojara_D12]

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

[Dangar332]

Hi, @Poojara_D12 

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

[rohit1991]

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.

[Kedar_Pande]

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