topic Re: My Total for previos year fiscal is not coming right in DAX Commands and Tips

My Total for previos year fiscal is not coming right

huzefa_53 — Tue, 19 Apr 2022 06:12:44 GMT

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

Re: My Total for previos year fiscal is not coming right

tamerj1 — Tue, 19 Apr 2022 06:47:25 GMT

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

Re: My Total for previos year fiscal is not coming right

huzefa_53 — Tue, 19 Apr 2022 07:12:12 GMT

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))

Re: My Total for previos year fiscal is not coming right

huzefa_53 — Wed, 20 Apr 2022 10:17:06 GMT

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

Re: My Total for previos year fiscal is not coming right

v-zhangti — Fri, 22 Apr 2022 08:53:10 GMT

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/1980704#M43662

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.

Re: My Total for previos year fiscal is not coming right

tamerj1 — Sat, 23 Apr 2022 04:58:49 GMT

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

Re: My Total for previos year fiscal is not coming right

Whitewater100 — Sat, 23 Apr 2022 14:34:45 GMT

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))