Can we apply MonthName(DateField) after Group By Clause

[Arindam_Ghoshal_13]

Hello Experts,

I am facing a situation where I cant use MonthName(DateField) in my FACT due to circular reference issue with calender & other Fact in my data model.


Here I want to apply MonthName(DateField) after Group By Clause.

Error - Invalid Expression
Below is my code.

FACT1:
Load * Inline [
Date,                 Amount
11-05-2022,   54
11-05-2022,   51
09-05-2022,   12
09-05-2022,   23
09-05-2022,   34
10-05-2022,   23
10-05-2022, 43
10-05-2022, 23
];

FACT2::
Load
MonthName(MonthStart(Floor(Date)-365)) as LY_Month,
MonthName(MonthStart(Floor(Date))) as CY_Month,
SUM(Amount) as Grp_Amount

Resident FACT1

Group By
//MonthStart(Floor(Date))
//MonthName(MonthStart(Floor(Date)))

MonthName(Date#([Date],'DD-MM-YYYY'))

Order By
Date
;

//Drop Table FACT1;
Exit Script;

I want to acheive MonthName() functionality after group by.

Fact2 is having complex calculated measure which I will use in Dashboard set expression for better performance.

Please any suggestions will be helpful.

[sidhiq91 · Accepted solution]

@Arindam_Ghoshal_13  Please see the below script, You can avoid using Order by Date since you are grouping them into Month.

NoConcatenate
FACT1:
Load * Inline [
Date, Amount
11-05-2022, 54
11-05-2022, 51
09-05-2022, 12
09-05-2022, 23
09-05-2022, 34
10-05-2022, 23
10-05-2022, 43
10-05-2022, 23
];

FACT2:
Load

Monthname(Date#(Date,'DD-MM-YYYY')) as CY_Name,
Monthname(Monthstart((Date#(Date,'DD-MM-YYYY')-365))) as LY_Month,
SUM(Amount) as Grp_Amount

Resident FACT1
group by
Monthname(Date#(Date,'DD-MM-YYYY')),Monthname(Monthstart((Date#(Date,'DD-MM-YYYY')-365)));

Drop table FACT1;

Exit Script;

Let me know if you are looking for something else, otherwise please like and accept it as a solution.

[Arindam_Ghoshal_13]

Thanks🙏
worked

[JordanWayne]

Znam dzisiaj najnowszą stronę z dzwonki na telefon