Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a tabular model where i have to create a measure to calculate Running total for last 3 months.The expected output has a grain to be per site per month.The issue is: fact table and month dimension are joined using Report Month field and date dimension has Report Month as FK.This fact table does not have day level data.
How do i acheive this?Attached a sample data
Solved! Go to Solution.
Hi pBI1234,
To achieve your requirement, you can create a measure and use DAX like this:
Measure = VAR from_ = EOMONTH(MAX(Table1[ReportMo]), -4) VAR end_ = MAX(Table1[ReportMo]) RETURN CALCULATE(SUM(Table1[Scrap]), FILTER(ALLEXCEPT(Table1, Table1[Site]), Table1[ReportMo] > from_ && Table1[ReportMo] <= end_))
Regards,
Jimmy Tao
Regards,
Jimmy Tao
Hi pBI1234,
To achieve your requirement, you can create a measure and use DAX like this:
Measure = VAR from_ = EOMONTH(MAX(Table1[ReportMo]), -4) VAR end_ = MAX(Table1[ReportMo]) RETURN CALCULATE(SUM(Table1[Scrap]), FILTER(ALLEXCEPT(Table1, Table1[Site]), Table1[ReportMo] > from_ && Table1[ReportMo] <= end_))
Regards,
Jimmy Tao
Regards,
Jimmy Tao
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, particularly the part about not posting images of data but rather text that can be copied.
But, in general you need to use an ALL or ALLEXCEPT in your measure calculation to override the relationship context.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.