Data Table Collection

[Anonymous]

Hello,

I have 2x tables one table which collects live results the other table we would like too reference results per. week / year.  In an attempt to accomplish this task I have create a DAX script which dosn't work as expected.

Listed below are 2x tables CROutages & CROresults i.e. CROresults Table is really what we are attempting to accomplish, but becaue of my lack of scripting skills the DAX script below provides total SUM of what is being collected not weekly.

Any help would be greatly appreciated,

Don

DAX Script below does not work only show total outage duration not weekly results.

Results = 
CALCULATE(SUM(CROutages[Outage Duration]),

FILTER(ALL(CROutages[Week],CROutages[Year]) ,
        'CROresults'[Week] <= 'Key Measures'[Reporting Week] &&
        'CROutages'[Year] = YEAR(TODAY())) 


)

Table(CROutages)

Week	Outage Duration
3	1982
4	213
5	16623
6	532
7	120

Table(CROresults) below is the results we are looking for

Year	Week	Results
2020	1	0
2020	2	0
2020	3	1982
2020	4	213
2020	5	16623
2020	6	532
2020	7	120
2020	8	0
2020	9	0

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

You can refer the DAX below:

Column = IF(CROresults[Year]=YEAR(TODAY()),LOOKUPVALUE(CROutages[Outage Duration],CROutages[Week],CROresults[Week]))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Not sure what is the problem but I'm guessing that you need a date dimension. Look it up on the web.

Once created, it will be far easier to run these time intelligence reports.