Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello All,
I have :
Fact_Sales table (Fact table) with Fk_Dim_Date as date key
Dim_Product table
Dim_Date
Dim_Period
I used a DAX query with interval date and Product_key as input to have the last Sales fact of every month in the Date Interval
Exp:
Interval date: "27/09/2021" - "05/10/2021"
Productkey: "P001"
What we have as records:
What we want: last record in fact in every Month, so we want the two records in red
I have the last date for every month but i havent the right result for Capital, Net ...
Thanks in advance
Hi @medridha
Is your issue resolved? If you still have problems on it? Could you please provide some sample data related to this dax formula and let me how you create relationships for these tables?
Best Regards,
Community Support Team _ Caiyun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it or I misunderstand your needs, please feel free to let us know. Thanks a lot!
Hi @amitchandak
Thanks for your reply, i should have an Evaluate DAX Query (the query result is used in Excel file)
The actual query :
EVALUATE
SELECTCOLUMNS (
SUMMARIZECOLUMNS (
'Dim_Product'[Product_Key],
'Dim_Period'[Period_Key],
'Dim_Date'[Date],
'Dim_Date'[FirstOfMonth],
FILTER (
VALUES ( 'Dim_Product'[Product_Key] ),
( 'Dim_Product'[Product_Key] = "PR001" )
),
FILTER (
VALUES ( 'Fact_Sales'[FK_Dim_Date] ),
Fact_Sales[FK_Dim_Date]
>= DATEVALUE ( "27/09/2021" ) + TIMEVALUE ( "27/09/2021" )
&& Fact_Sales[FK_Dim_Date]
<= DATEVALUE ( "05/10/2021" ) + TIMEVALUE ( "05/10/2021" )
),
FILTER (
VALUES ( 'Dim_Date'[Date] ),
'Dim_Date'[Date]
>= DATEVALUE ( "27/09/2021" ) + TIMEVALUE ( "27/09/2021" )
&& 'Dim_Date'[Date]
<= DATEVALUE ( "05/10/2021" ) + TIMEVALUE ( "05/10/2021" )
&& 'Dim_Date'[Date] = LASTDATE ( Fact_Sales[FK_Dim_Date] )
),
"NetFlat", CALCULATE ( [NetFlat], FILTER ( Dim_Period, Dim_Period[Period_Key] = "P1" ) ),
"CumulReturn",
CALCULATE (
TOTALYTD ( [NetFlat], Dim_Date[Date] ),
FILTER ( Dim_Period, Dim_Period[Period_Key] = "P1" )
),
"CAP_1",
CALCULATE (
[CAP],
PARALLELPERIOD ( Dim_Date[Date], -1, MONTH ),
FILTER ( Dim_Period, Dim_Period[Period_Key] = "P1" )
),
"CAP", CALCULATE ( [CAP], FILTER ( Dim_Period, Dim_Period[Period_Key] = "P1" ) ),
"MVP", [MVP]
),
"Product", [Product_Key],
"Period", [Period_Key],
"FirstDate", Dim_Date[FirstOfMonth],
"LastDate", 'Dim_Date'[Date], //"LastDate", LASTDATE ( Fact_Sales[FK_Dim_Date] ),
"Capital N-1", [CAP_1],
"Capital N", [CAP],
"Net", [NetFlat]
"MVP" , [MVP]
)
When i replace 'Dim_Date'[Date] by 'Dim_Date'[MonthName] i have the right date (only two rows but i havent the right value for measure like CAP, Net ...
@medridha , You can have a measure like
calculate(lastnonblankvalue('Date'[Date],[Captial N]), allexcept(Table, Table[Product], [Period], [Month Year]))
or
calculate(lastnonblankvalue('Date'[Date],[Captial N]), allexcept(Table, Table[Product] = max(Table[Product]) && [Period] = max([Period]) && [Month Year] = max( [Month Year]) ))
User | Count |
---|---|
50 | |
24 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |