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
Anonymous
Not applicable

Cube.ApplyParameter selection current week and last week

 

For the week selection I have found in the community forum this solution

(dDate as date) =>
let
    Quelle = if
Number.RoundDown((Date.DayOfYear(dDate )-(Date.DayOfWeek(dDate , Day.Monday)+1)+10)/7)=0
 
then 
Number.ToText(Date.Year(dDate)-1)& Text.End("0" & Number.ToText(Number.RoundDown((Date.DayOfYear(#date(Date.Year(dDate )-1,12,31))-(Date.DayOfWeek(#date(Date.Year(dDate )-1,12,31), Day.Monday)+1)+10)/7)),2)
 
else if
(Number.RoundDown((Date.DayOfYear(dDate )-(Date.DayOfWeek(dDate , Day.Monday)+1)+10)/7)=53
and (Date.DayOfWeek(#date(Date.Year(dDate ),12,31), Day.Monday)+1<4))
 
then
Number.ToText(Date.Year(dDate)+1)&"01"
 
else 
Number.ToText(Date.Year(dDate))& Text.End("0"&Number.ToText(Number.RoundDown((Date.DayOfYear(dDate)-(Date.DayOfWeek(dDate, Day.Monday)+1)+10)/7)),2)
in
    Quelle

 and had to save this in a new quary as DateToYYYYWW and had to add in my original quary this one

 

let
CurrentYYYYWW= DateToYYYYWW(Date.From(DateTime.LocalNow())),
NextWeek = DateToYYYYWW(Date.AddDays(Date.From(DateTime.LocalNow()),7))
in NextWeek

 

let
Source = SapBusinessWarehouse.Cubes("E9Esap.nestle.com", "E9E", "102", "USERS", [Implementation="2.0", LanguageCode="EN"]),
Y6DS1MC0 = Source{[Name="Y6DS1MC0"]}[Data],
#"Y6DS1MC0/Y6DS1MC0Q_001" = Y6DS1MC0{[Id="Y6DS1MC0/Y6DS1MC0Q_001"]}[Data],
#"Added Items" = Cube.Transform(#"Y6DS1MC0/Y6DS1MC0Q_001",
CurrentYYYYWW= DateToYYYYWW(Date.From(DateTime.LocalNow())),
{
{Cube.ApplyParameter, "[YMMPAREA]", {{"[YPLGAREA].[D]"}}},
{Cube.ApplyParameter, "[YPMUNIT]", {"[YUNITS].[PUM]"}},
{Cube.ApplyParameter, "[YSOCLMTH]", {{}}},
{Cube.ApplyParameter, "[YSALESOR]", {{"[0SALESORG].[NL10]"}}},
{Cube.ApplyParameter, "[YSNPCPH2]", {{"[YMAT_COMP__0PRODH2].[NLK]"}}},

 

But I get this message.

 

Expression.Error: The name 'CurrentYYYYWW' wasn't recognized. Make sure it's spelled correctly.

 

Does anyone knows how to solve this?

 

1 REPLY 1
lbendlin
Super User
Super User

Your Cube.Transform call is not in the right format.

 

Cube.Transform - PowerQuery M | Microsoft Learn

 

You may want to clear out the sensitive information from your examples.

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
Top Kudoed Authors