How to Get fiscal year lastmonth value in DAX?

[hari_bi]

The above one is my scenario , i need to show FiscalYear last moth value in Other column like "Sales2".

But Sales are caluated measure like "Sales := Actuals/totalcast ".

[v-danhe-msft · Accepted solution]

Hi @hari_bi,

You could create this measure:

Measure 4 = var a= MAXX(ALL('Date'),IF(ISBLANK([E_onHandsMonthsCover]),BLANK(),CALCULATE(MAX('Date'[Year Month]),ALLEXCEPT('Date','Date'[Year Month]))))
Return IF(ISBLANK([E_onHandsMonthsCover]),BLANK(),IF(MAX('Date'[Year Month])=a,[E_onHandsMonthsCover],CALCULATE([E_onHandsMonthsCover],FILTER(ALL('Date'),'Date'[Year Month]=a))))

Result:

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/lk9uvrpc3lf4d0q/Local%20Copy%20R%26D.pbix?dl=0

Regards,

Daniel He

[v-danhe-msft]

Hi @hari_bi,

Based on my test, you could refer to below steps:

Sample data:

Create a calculated column and you could see the result:

Sales2 = CALCULATE(SUM(Table1[Sales]),FILTER('Table1','Table1'[Fisacl Month]=MAX('Table1'[Fisacl Month])))

Regards,

Daniel He

[hari_bi]

Hi v-danhe-msft,

Thanks for your replay,

Here small update from my side.

Sales is Calcualted Measure not a column like Sales = A/B , here A is another Calcualted Measure and B is also Calcualted Measure and FiscalMoth column is avaialble in Date table not in the same table.

when i do sum, it will showing only available colums in a perticular table not able to find the Calcualted Measure.

I tried to change the formula like below but I am getting error in SUM() and Max() function are not accepting the Calcualted Measures.

Sales2 = CALCULATE(SUMX('Customer Depletions',FILTER('Customer Depletions',RELATED('Date'[Year Month]=
MAX(RELATED('Date'[Year Month]))))))

even i am not able to create Sales2 as a calculated column , Can you provide your suggestion on the above formula.

[v-danhe-msft]

Hi @hari_bi,

Based on my test, I have create the sales as measure:

Sales measure = CALCULATE(SUM(Table1[Sales]))/1

And you could modify the former formula as:

Sales2 = CALCULATE([Sales measure],FILTER('Table1','Table1'[Fiscal Month]=MAX('Table1'[Fiscal Month])))

Now you could get the correct result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/ewivkb1cjypbt34/How%20to%20Get%20fiscal%20year%20lastmonth%20value%20in%20...

Regards,

Daniel He

[hari_bi]

Hi Daniel He,

I am sharing my PBI repeort with you can you create last moth value as new column.

Page name : Last month Sales , and as per my requirment data avaialbe in table visual.

Thanks in Advance.

like for PBI Report: https://drive.google.com/file/d/1ktOVLG7FzQbsnsX7h9GgZZ4qHcbioFZW/view

Regards,

Hari.

[v-danhe-msft]

Hi @hari_bi,

I have no access to the pbix file, could you please give me the access to the file?

Regards,

Daniel He

[hari_bi]

Hi Daniel He,

Sorry for the inconveniance,

Can you check this path once , i thinks you can access the PBI report now.

https://www.dropbox.com/s/lnvnttc4tvmiq7k/Local%20Copy%20R%26D.pbix?dl=0

Thank you,

Hari.

[v-danhe-msft · Accepted solution]

Hi @hari_bi,

You could create this measure:

Measure 4 = var a= MAXX(ALL('Date'),IF(ISBLANK([E_onHandsMonthsCover]),BLANK(),CALCULATE(MAX('Date'[Year Month]),ALLEXCEPT('Date','Date'[Year Month]))))
Return IF(ISBLANK([E_onHandsMonthsCover]),BLANK(),IF(MAX('Date'[Year Month])=a,[E_onHandsMonthsCover],CALCULATE([E_onHandsMonthsCover],FILTER(ALL('Date'),'Date'[Year Month]=a))))

Result:

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/lk9uvrpc3lf4d0q/Local%20Copy%20R%26D.pbix?dl=0

Regards,

Daniel He

[hari_bi]

Hi Daniel He,

Thank you for your help to get last month value.

Small update on the same requirement , Please find the below tabale:

Now i have one more column "Reporting Month" added newly.

If Reporting Moth = Dec-17 , i need to search that "Dec-17" in Fiscal Month Column first then what ever the value for that month in "Months Cover" column ,  i need display the same value as new column.

Here i am sharing my PBI file for the same , present in PBI file data not available.

https://www.dropbox.com/s/nnw8mj12d3e2xj0/Month%20Match.pbix?dl=0

Thank you.

Hari

[hari_bi · Accepted solution]

Hi Daniel He,

Thanks for your replay , Its working as per my requirment.

But i did the same in tabular model live connection , but i am getting error like below:

"The function MAX takes an argument that evaluates to numbers or dates and cannot work with values of type String."

here i pass [MonthId] which is integer column.

I thik this will work.

Thank you for healping out to design DAX.

regards,

hari

[hari_bi]

https://drive.google.com/drive/my-drive