DAX Help

[crjackson]

Hello!

I am trying to calculate an annualized compliance percentage. I have the denominators for each task in a table. The problem is that the denominators change annually. For example, for tasks for 2022 that are done monthly, the denominator is 12, quarterly is 4, and for some introduced mid-year is 6. In 2023, the task denominators will mostly be 12 or 4. I need both years in my table so they can look at the historical data. It seems to be ignoring the date filter and either adding the denominators from both years for a particular task or giving me the max denominator of the two. Is there a way to convert the column values to a measure that can be used to find the percentage? 

[amitchandak]

@crjackson ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

I usually break such data using range

Between Dates - Dates between
Tables
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

[crjackson]

Visual: Den_SUM is not calculating properly – it is adding both years together regardless of employee or year.

Data Tables:

Denominators:

Den_SUM is adding them together regardless of employee ID or date

Den_SUM = SUMX(Denominators,Denominators[Denominator])

Compliance Data:

The sum measure is working for Comp_SUM = SUMX('Compliance Data','Compliance Data'[CompTotals])

[FreemanZ]

it would be easier if you could provide some sample data with expected result.

[crjackson]

Visual: Den_SUM is not calculating properly – it is adding both years together regardless of employee or year.

Data Tables:

Denominators:

Den_SUM is adding them together regardless of employee ID or date

Den_SUM = SUMX(Denominators,Denominators[Denominator])

Compliance Data:

The sum measure is working for Comp_SUM = SUMX('Compliance Data','Compliance Data'[CompTotals])

[Anonymous · Accepted solution]

Hi @crjackson ,

You are using employee and year from Compliance table as slicer.

In this case you will need an active relationship between Denominators and  Compliance tables.

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Jay