Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LisaChung
Frequent Visitor

Month to date based on customized column

Hi,

 

I try to generate month to date based on my customized logic.
As below, I would like to generate a measure in "Ordered Qty MTD" based on "Weeknum&year&month" column which is generated based on "Date" column.  In the visualization, I tried to use time inteliigence, but still fail. Wonder if you could help?

My intention is to only list down by "Weeknum&year&month", "Material No", "Ordered Qty" and "Ordered Qty MTD" columns without showing date, but time intelligence dax would not allow to do so. Thanks.

 

LisaChung_0-1652766952327.png

 

1 ACCEPTED SOLUTION

Hi @LisaChung 
Have you had the chance to check my solution?

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

Hi @LisaChung 
Please try

Qty MTD =
VAR CurrentDate =
    MAX ( TableName[Date] )
VAR StartOfMonth =
    DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ), 1 )
RETURN
    CALCULATE (
        SUM ( TableName[Ordered Qty] ),
        ALLEXCEPT ( TableName, TableName[Material No] ),
        TableName[Date] <= CurrentDate,
        TableName[Date] >= StartOfMonth
    )

Hi @LisaChung 
Have you had the chance to check my solution?

thanks for helping. It works fine.

tamerj1
Super User
Super User

@LisaChung 
Do you have a datetable? is [Weeknum&year&month] a column? In which table?

Hi @tamerj1 

 

This is the only table I have.  I made "Weeknum&year&month" column on my own. 

They are all in this table. Thanks

 

DateWeeknum&year&monthMaterial NoOrdered Qty
2022/05/01Week 1 of MayA0
2022/05/02Week 2 of MayA3650
2022/05/03Week 2 of MayA23650
2022/05/04Week 2 of MayA2900
2022/05/05Week 2 of MayA450
2022/05/06Week 2 of MayA20550
2022/05/07Week 2 of MayA400
2022/05/09Week 3 of MayA1900
2022/05/10Week 3 of MayA3500
2022/05/11Week 3 of MayA850
2022/05/12Week 3 of MayA1100
2022/05/13Week 3 of MayA250

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors