Order Report (DAX Expression Help)

Hi.

First piece of advice: Don't take advice from people who are themselves new to Power BI and DAX. They very often promote very bad habits they are not even aware of. The result is not only awkward models they set up but also incorrect DAX they write thinking everything's working. They are then not even able to spot issues. DAX is simple but very dangerous if used without knowledge.

Second, try to learn something about good model design before you embark upon a project. It'll save you countless hours of scratching your head and not being able to figure out why the figures are wrong. Worse, if the model is wrong, no DAX will fix it.

Third, I'll give you some advice about good design.

1. Always create star/snowflake schemas out of your tables.

2. Never, ever join 2 fact tables directly, only through dimensions.

3. Fact tables should never be exposed. They must be hidden.

4. Dimensions should be conformed and connected to your facts.

5. Slicing is done only through dimensions, never directly on fact tables.

6. Always create calendars for the relevant date columns in your fact tables and mark them as DATE TABLE in the model. This will enable time-intelligence functions.

7. Use one-directional filtering 99% of the time. Bi-directional filtering is DANGEROUS and before you decide to use it, know very well the consequences of doing so. Many people think they should have bi-dir filtering on for all relationships. THIS IS SOOOOOOOOOO WRONG. They'll be producing wrong numbers before they know it and will not even be able to spot the issue.

These are some of the golden rules of dimensional modeling with DAX and PBI. If you stick to them, you'll save yourself a lot of grief and head-scratching. Even more, you'll be able to write simple and predictable DAX.

Now, when the model in RIGHT, your measures are easy to write:

// Bear in mind Calendar must be
// a Date Table connected to the
// fact table and marked as
// DATE TABLE in the model. Calendar
// must contain ALL SEQUENTIAL dates 
// covering all the years found in
// the fact table. Anything else...
// and time-intel functions will not
// work correctly.
MTD =
var __oneMonthVisibleOnly =
	HASONEVALUE( 'Calendar'[YearMonth] )
var __mtd =
	CACULATE(
		[Measure],
		DATESMTD( 'Calendar'[Date] )
	)
return
	if( __oneMonthVisibleOnly, __mtd )

The others are similar...

Best

D