Calculate a couple of SUM measures

[RossBateman]

Hi guys

I'm trying to create some measure based on a couple of different SUM forumas but am not having any luck!

I'll add some screenshots but currently in one table i have the following:

-Financial year

-Tranaction period (13 per FY)

-Posting Code

-Jrn desc

-Jrn amount

-Date

And in a linked mapping table I have the following:

-Posting Code

-Desc

_Catergory (Level 1)

-Category (Level 2)

I've got a measure as the following which works comepletely fine based on a slicer for period (only allows a year to date view to be selected, not individual periods):

CAstock = calculate(sum([journal_amount]), ('BSNominalMapping'[Bal level2] ="Stock"))

On the same page I'd like to create a measure for a year to date view at the end of every month and also one for each month and period individually.

Would someone be able to help me out?

Many thanks,

Ross

[amitchandak]

@RossBateman , Not very clear

YTD =calculate([CAstock]),DATESYTD('Date'[Date]))
MTD = calculate([CAstock]),DATESMTD('Date'[Date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

Refer to my webinar

https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[RossBateman]

Sorry if I was not that clear!

What I'm trying to do is create 12 measures (one for each month) that return the values in the below formula but are split into the months.

CAstock = calculate(sum([journal_amount]), ('BSNominalMapping'[Bal level2] ="Stock"))

Then using the 12 measures that have been created it should be easy to create 11 more measures for the year to date amount at the end of each month.

[amitchandak]

@RossBateman , What you want to get using 12 measure. Can you explain.

[RossBateman]

@amitchandak From the nominal table (screenshoted above) I'd like a measure to sum all of the jrn amounts based on a categgory in the mapping table but only containg transaction that occured in a certain month, so ignoring any slicer filters on a report level.

[v-lili6-msft]

hi  @RossBateman 

Could you use some simple sample data and share what is your expected output, that will be a great help.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[RossBateman]

Nominal ledger tableMapping Table