My Total for previos year fiscal is not coming right

[huzefa_53]

This is my table structure  

Income =

//CALCULATE([Total Postings],'GL Accounts'[account_type] ="Income", 'Calendar'[Date])

SUMX(SUMMARIZE( FILTER('GL Accounts','GL Accounts'[account_type] ="Income"),'GL Accounts'[account_type],"postings",[Total Postings]),[postings])

Income PY = //CALCULATE([Total Postings],'GL Accounts'[account_type] ="Income", SAMEPERIODLASTYEAR('Calendar'[Date]) )

var fiscalYear = MAXA('GL Periods'[fiscal_year])-1

var periodNo = SELECTCOLUMNS(Filter(ALL('GL Periods'), 'GL Periods'[fiscal_year] = fiscalYear),"periodNo",'GL Periods'[period_number])

var selected_periods = SELECTCOLUMNS(Filter(ALL('GL Periods'), 'GL Periods'[fiscal_year] = fiscalYear && 'GL Periods'[period_number] in periodNo ), "periodIds", 'GL Periods'[period_id])

var maxPeriodEndDate = [Max Selected Period PY Date]

var bal= FILTER(ALL('GL Postings'), 'GL Postings'[period_id] IN selected_periods && RELATED('GL Accounts'[account_type]) = "Income")

var bal2= FILTER(ALL('GL Postings'),

'GL Postings'[glAccount_id] IN VALUES( 'GL Accounts'[account_id]) && 'GL Postings'[trans_date] <= MAXA('Calendar'[PYDate]))

return

//CALCULATE([Total Actual], bal)

//SUMX( SUMMARIZE(FILTER('testable','testable'[period_id] in selected_periods),testable[period_id],"incomepy",SUM(testable[amount])) ,[incomepy])

CALCULATE( SUM(testable[amount]) , FILTER(testable,testable[period_id] in selected_periods))

//SUMX( SUMMARIZE(FILTER(bal,RELATED('GL Accounts'[account_type]) = "Income"),'GL Periods'[period_number_string],"income",[Total Postings]),[income])

//SUMX(SUMMARIZE(bal,'GL Postings'[glAccount_id],"income",[Income]),[income])

//CALCULATE( [Income] ,FILTER(ALl('GL Postings'),'GL Postings'[period_id] IN selected_periods &&

//'GL Postings'[glAccount_id] IN VALUES( 'GL Accounts'[account_id]) && 'GL Postings'[trans_date] <= maxPeriodEndDate) )

//SUMX(FILTER('GL Accounts', 'GL Accounts'[account_type] ="Income"),SUMX(bal,[Income]))

//return

//CALCULATE([Income], bal)

//VAR postings = SUM('GL Postings'[amount])

//return

//CALCULATE(SUM('GL Postings'[amount]),FILTER('GL Accounts', 'GL Accounts'[account_type] ="Income"), PARALLELPERIOD('Calendar' [Date], -1, YEAR))

//CALCULATE(SUM('GL Postings'[amount]),FILTER('GL Accounts', 'GL Accounts'[account_type] ="Income"), 'Calendar'[PYDate])

//CALCULATE([Income], 'Calendar'[PYDate]))

i have tried many solution using sumx , summarize i got row previous year income right but the total doesnt come right in any case

[Whitewater100]

Hi:

Are you able to get your prior year result using the following.

* Note - assumption your measure for income is correct. 

Income YTD = TOTALYTD([Income], 'Calendar'[Date],  "30-6")  this gives current fiscal year (July-June)-stopping at June 30.

I see your reports is build on fiscal & monthly. You can also add a month index column to your Calendar table so you can sort your months easier.

First Calc Col in Calendar

MONTH No. = MONTH(Calendar[Date])

Next Calc Column in Calendar Table (used or sort order)

Fiscal Month No. = IF(Calendar[Month] <= 6, Calendar[month] + 6, Calendar[Month] -6)

Hope this helps.

PY Income = CALCULATE([Income YTD], DATEADD(Dates[Date], -1,YEAR))

[tamerj1 · Accepted solution]

Hi @huzefa_53 
Here is a sample file with the solution https://www.dropbox.com/t/VtLpsawmW0B3XWGE

Income PY - Tamer = 
VAR PreviousYearPeriodStart = MIN ( period[ID] ) - 12
VAR PreviousYearPeriodEnd = MAX ( period[ID] ) - 12
VAR Result =
    CALCULATE ( 
        [Income],
        REMOVEFILTERS ( period ),
        period[ID] >= PreviousYearPeriodStart,
        period[ID] <= PreviousYearPeriodEnd
    )
RETURN
    Result

[huzefa_53]

I am sharing the link of pbix file so you can look properly

What i want is that a Dax that break on fiscal period and i could get month wise sum and than sum of all months equivalent in order to compare current income and previous year income based on fiscal year we manage fiscal year details in different table

https://drive.google.com/file/d/1uomsgP_5zqqnVKxpxxcJosZ6lmXi4Vzz/view?usp=sharing 

[v-zhangti]

Hi, @huzefa_53 

Your date is a bit complicated, I didn't finish reading it, but about calculating the value of the previous year, you can try this function.

Measure = 
CALCULATE([Total Postings],'Acc'[account_type] ="Income", DATEADD('Date'[Date],-1,YEAR))

Use the DATEADD function to replace the description of the previous year in your calculation.

Please refer to this link.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Previous-year-Previous-to-previous-year/m-p/1... 

Best Regards,

Community Support Team _Charlotte

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

[tamerj1]

Hi @huzefa_53 
Did you try slicing by "Month - Year" column instead of "Month Name" column? As the Month Name column contains all years therfore, last year will have no meaning 

[huzefa_53]

yes still problem not resolved

i have change sturucture as well 

Income PY =

var fiscalYear = MAXA('GL Periods'[fiscal_year])-1

var periodNo = SELECTCOLUMNS(Filter(ALL('GL Periods'), 'GL Periods'[fiscal_year] = fiscalYear),"periodNo",'GL Periods'[period_number])

var selected_periods = SELECTCOLUMNS(Filter(ALL('GL Periods'), 'GL Periods'[fiscal_year] = fiscalYear && 'GL Periods'[period_number] in periodNo ), "periodIds", 'GL Periods'[period_id])

var maxPeriodEndDate = [Max Selected Period PY Date]

var bal= FILTER(ALL('GL Postings'), 'GL Postings'[period_id] IN selected_periods && RELATED('GL Accounts'[account_type]) = "Income")

var bal2= FILTER(ALL('GL Postings'),

'GL Postings'[glAccount_id] IN VALUES( 'GL Accounts'[account_id]) && 'GL Postings'[trans_date] <= MAXA('Calendar'[PYDate]))

return

CALCULATE( SUM(testable[amount]) , FILTER(testable,testable[period_id] in selected_periods))