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.
Hello,
I have a little issue.
I have a Canvass Table to define some custom Periods, the Columns are:
CanvassID int
CanvassName String
FromDate Date
ToDate Date
FiscalYear String
I created a Measure Named Current FY
Current FY =
CALCULATE (
DISTINCT ( Canvass[FiscalYear] );
FILTER (
Canvass;
(
Canvass[FromDate]
<= ( DATE ( YEAR ( NOW () ); MONTH ( NOW () ); DAY ( NOW () ) ) )
&& (
DATE ( YEAR ( NOW () ); MONTH ( NOW () ); DAY ( NOW () ) )
<= ( Canvass[ToDate] )
)
)
)
)
If I place in the chart Area it correctly print FY 16/17 now I want to calculate the FY Actuals from a Table of Values
JC FY Actuals =
CALCULATE (
SUM ( 'Canvass Budget Actuals'[JCActuals] );
FILTER (
'Canvass Budget Actuals';
[CanvassID]
= CALCULATE (
DISTINCT ( Canvass[CanvassID] );
FILTER ( Canvass; Canvass[FiscalYear] = [Current FY] )
)
)
)
The above measure doesn't work as expected, I get the correct results only if i Change [Current FY] with the string "16/17".
Any help?
Solved! Go to Solution.
@spaxia wrote:
Hello,
I have a little issue.
I have a Canvass Table to define some custom Periods, the Columns are:
CanvassID int
CanvassName String
FromDate Date
ToDate Date
FiscalYear String
I created a Measure Named Current FYCurrent FY =
CALCULATE (
DISTINCT ( Canvass[FiscalYear] );
FILTER (
Canvass;
(
Canvass[FromDate]
<= ( DATE ( YEAR ( NOW () ); MONTH ( NOW () ); DAY ( NOW () ) ) )
&& (
DATE ( YEAR ( NOW () ); MONTH ( NOW () ); DAY ( NOW () ) )
<= ( Canvass[ToDate] )
)
)
)
)If I place in the chart Area it correctly print FY 16/17 now I want to calculate the FY Actuals from a Table of Values
JC FY Actuals =
CALCULATE (
SUM ( 'Canvass Budget Actuals'[JCActuals] );
FILTER (
'Canvass Budget Actuals';
[CanvassID]
= CALCULATE (
DISTINCT ( Canvass[CanvassID] );
FILTER ( Canvass; Canvass[FiscalYear] = [Current FY] )
)
)
)The above measure doesn't work as expected, I get the correct results only if i Change [Current FY] with the string "16/17".
Any help?
I'd doubt it is a filter context issue. What's going on if you put measure [Current FY] as a table column. To got the correct result, try
Current FY =
CALCULATE (
DISTINCT ( Canvass[FiscalYear] ),
FILTER (
ALL(Canvass),
(
Canvass[FromDate]
<=DATEVALUE(TODAY())
&& (
DATEVALUE(TODAY())
<= ( Canvass[ToDate] )
)
)
)
)
For more specific suggestion, please post some sample(in plain text or a uploaded file link) data and expected output.
@spaxia wrote:
Hello,
I have a little issue.
I have a Canvass Table to define some custom Periods, the Columns are:
CanvassID int
CanvassName String
FromDate Date
ToDate Date
FiscalYear String
I created a Measure Named Current FYCurrent FY =
CALCULATE (
DISTINCT ( Canvass[FiscalYear] );
FILTER (
Canvass;
(
Canvass[FromDate]
<= ( DATE ( YEAR ( NOW () ); MONTH ( NOW () ); DAY ( NOW () ) ) )
&& (
DATE ( YEAR ( NOW () ); MONTH ( NOW () ); DAY ( NOW () ) )
<= ( Canvass[ToDate] )
)
)
)
)If I place in the chart Area it correctly print FY 16/17 now I want to calculate the FY Actuals from a Table of Values
JC FY Actuals =
CALCULATE (
SUM ( 'Canvass Budget Actuals'[JCActuals] );
FILTER (
'Canvass Budget Actuals';
[CanvassID]
= CALCULATE (
DISTINCT ( Canvass[CanvassID] );
FILTER ( Canvass; Canvass[FiscalYear] = [Current FY] )
)
)
)The above measure doesn't work as expected, I get the correct results only if i Change [Current FY] with the string "16/17".
Any help?
I'd doubt it is a filter context issue. What's going on if you put measure [Current FY] as a table column. To got the correct result, try
Current FY =
CALCULATE (
DISTINCT ( Canvass[FiscalYear] ),
FILTER (
ALL(Canvass),
(
Canvass[FromDate]
<=DATEVALUE(TODAY())
&& (
DATEVALUE(TODAY())
<= ( Canvass[ToDate] )
)
)
)
)
For more specific suggestion, please post some sample(in plain text or a uploaded file link) data and expected output.
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.
User | Count |
---|---|
115 | |
100 | |
88 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |