Issue with Measure Changing Dynamically II

[kressb]

I have several issues with a measure changing dynamically.

I need to take Total Amount for a Year, Divided by a Base to get a Calculation (the measure).
It must flex with each year, so for this year, it must prorate the Calculation since the year is not complete.

Relationship of Tables:

DAX Formula:

Calc =
    Var _Amount =
                Calculate(sum(Amounts[Amounts]),
                                Filter(Amounts,Amounts[Column]=Value)
                                )
    Var _Base = Average(Base[Base])
    Var _Num = Calculate(DistinctCountNoBlank(Amounts[MonthYr],Allselected())
    Var _Denom = DistinctCountNoBlank(Amounts[FiscalYear])*12
Return
Divide(_Amount,_Base*(_Num/_Demon),0)

If I use a slicer and look at each Year individually, this gets me the correct answer, but an incorrect Total.

If I look at both Years together, nothing is correct.

**EDIT:

I have tried a very small update to the above.

for Var_Num I have done:

Var _Num = DistinctCountNoBlank(Amounts[MonthYr])

This gets me much closer to the the correct total, but:

 - If the Slicer is set to ALL the data is still incorrect for all lines.

 - I still cannot get the Matrix Sum to Match a "Card" Sum..

Any thoughts? TYIA

[V-pazhen-msft · Accepted solution]

@kressb 
You cannot use earlier like that in a measure, instead we normally use ALLSELECTED and MAX().

  Var _Num = Calculate(DistinctCountNoBlank(Amounts[MonthYr]),Filter(ALLSELECTED(Amounts),Amounts[Fiscal Year]=MAX(Amounts[Fiscal Year]))

Paul Zheng _ Community Support Team

[kressb]

@V-pazhen-msft thank you!! this works quite well!

[Greg_Deckler]

@kressb This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

If you can post data as text, can recreate.

[kressb]

@Greg_Deckler 
**Edit

Ok here is my first issue - there is not necessarily an "Amount" for every month of the year. 

Ex:

   Fiscal Year 2020: "Nineteen" only had data for April, May, and June.

       I still need it to calculate as 12 Mons Total / 12 Mons Complete, but 
       Var _Num = DistinctCountNoBlank(Amounts[MonthYr])

            -- > only returns 3

        Var _Num = Calculate(DistinctCountNoBlank(Amounts[MonthYr]),Allselected())

           --> Returns 21 (ie adds 12 from first year and 9 from second year which isn't correct when the column is for 2020 only)

How do I return the maximum count regardless as to if that Name as a value for all months?
I wanted to do something like:

  Var _Num = Calculate(DistinctCountNoBlank(Amounts[MonthYr]),Allselected(),Filter(Amounts,Amounts[Fiscal Year]=Earlier(Amounts[Fiscal Year])
    but I can't use Earlier in a Measure Context... 

Amounts Table: